SQL Notes, Tips & Tricks

typecasting is done in SQL via double-colon :: (e.g var::integer)

As with other languages, NULL should represent non-existent data. In SQL, null is just represented as NULL. Columns can be set NOT NULL, which means every row must have some data for that column. In SQL, unlike PHP, NULL does not cast to zero. So NULL + 2 = NULL, and NULL < 2 evaluates to NULL, not TRUE.

COALESCE() lets you specify a default value for NULL, e.g COALESCE(myColumn, 0) will return 0 if myColumn is null.

NULLIF() does the opposite… if you say NULLIF(myColumn, 3), it will return the value of myColumn unless it equals 3, at which point it will return NULL

ORDER BY USING: lets you specify an arbitrary sort function. Not quite sure how to use this yet. You can also do stuff like ORDER BY col1 + col2, which is cool.

Dates: Date formatting can be handled with DATESTYLE, but is probably just as easy to handle it in the application layer. INTERVAL is used for intervals … e.g. 21 days dates after April 5 would be ('2008-04-05'::timestamp - INTERVAL '21 days')

Pattern matching: there is always the nasty LIKE and NOT LIKE, but Postgres supports regular expressions too