Postgres Cheat Sheet

A quick reference to postgreSQL 9.x syntax

Postgres Cheat Sheet

Just a quick reference for postgres - datatypes, queries, other random code snippets.

Query Information

Select statement

SELECT title, director, year FROM tablename WHERE genre = "thriller" GROUP BY year ORDER BY title;

Funky Joins

SELECT, t1.image_link, t1.title, t1.source_url, array_agg(t2.tag_phrase) FROM holymonkey t1 LEFT JOIN tagtable t2 ON = t2.tag_id WHERE (SELECT id from holymonkey where domain='') GROUP BY ORDER BY ASC

Modify Table

Add a column

ALTER TABLE film ADD COLUMN director varchar(50);

Modify a column

ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name SET NOT NULL, ALTER COLUMN gender DROP NOT NULL, ALTER COLUMN phone TYPE numeric(12) USING phone::numeric ;

Delete a column



Restrict column values to a list

CREATE TABLE sometable ( colors TEXT CHECK (colors IN ('red', 'green', 'blue')) ) -- Note: Double quotes won't work (they'll look for a field)

Data Types

Commonly used datatypes

smallint (2 bytes) int (4 bytes) bigint (8 bytes) serial bigserial numeric(p) // fixed length numeric(p,s) // decimal fixed length char(n) // Fixed Length varchar(n) text date time(p) timetz(p) timestamp(p) timestamptz(p)