Collection of Snippet from Postgres Query

postgre_snippets.sql

-- Grant For Schema
GRANT ALL PRIVILEGES ON SCHEMA pdg TO project1;
-- Grant For Table
GRANT ALL PRIVILEGES ON TABLE pdg.proposal TO project1;
-- Grant For Sequence
GRANT USAGE, SELECT ON SEQUENCE pdg.proposal_id_seq TO project1;
-- Grant For View
GRANT SELECT ON pdg.vw_program_hierarchy TO project1;

-- //-- Source: http://www.postgresql.org/docs/8.3/static/sql-grant.html --//

-- Create Table
CREATE TABLE pdg.proposal__file_revision
(
  id integer NOT NULL,
  proposal_id integer NOT NULL,
  proposal_metadata character varying(500),
  file_id integer,
  revision_no smallint,
  note character varying(1000),
  created_at timestamp without time zone,
  created_by integer,
  CONSTRAINT proposal__file_revision_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE pdg.proposal__file_revision OWNER TO postgres;
GRANT ALL ON TABLE pdg.proposal__file_revision TO postgres;
GRANT ALL ON TABLE pdg.proposal__file_revision TO project1;
-- End Create Table

-- Create View
CREATE VIEW pdg.vw_employee_pdg AS
SELECT * FROM table_name WHERE condition=1 ORDER BY field
-- End Create View

-- Create Index
CREATE UNIQUE INDEX proposal_id_idx ON pdg.ipp USING btree (proposal_id); -- Unique
CREATE INDEX ipp_no_idx ON pdg.ipp USING btree (ipp_no COLLATE pg_catalog."default"); -- String
CREATE INDEX ipp_date_idx ON pdg.ipp USING btree (ipp_date); -- Date/Integer

-- ALTER COLUMN FROM varchar TO integer
ALTER TABLE pdg.proposal ALTER COLUMN proposal_type TYPE smallint USING (proposal_type::integer);

-- RENAME A COLUMN
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Select Enum Type (As Array)
SELECT ENUM_RANGE(null::pdg.project_file_types)
-- Select Enum Type (As Rows)
SELECT UNNEST(ENUM_RANGE(null::pdg.project_file_types))
-- Select Column From Table
SELECT column_name FROM information_schema.columns WHERE table_name='table_name';
-- Add New Enum Type
ALTER TYPE pdg.project_file_types ADD VALUE  'Lampiran RAB';

--- Update JOIN
UPDATE table1 AS t1
  SET company_id = t2.company_id
    FROM sdm_employee t2  
  WHERE employee_id = t2.id AND t1.company_id IS NULL

---
--- Convert rows result to array text
---
SELECT string_agg(id::text, ',') FROM pdg.email_queue WHERE notif_type='IPPCreate'

--- Show Columns
SELECT * FROM information_schema.columns
WHERE table_schema = 'schema' AND table_name = 'table'

---
--- Trivia
---
/* CURRENT_YEAR := date_part('year'::text, now())::text */