Last Updated: 27 Jun 2023

   |   

Author: dordal

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
backend-tech:sql:sql-notes [May 15, 2008 06:05 AM]
dordal 		backend-tech:sql:sql-notes [Jun 27, 2023 04:52 AM]
dordal old revision restored (Jun 25, 2023 06:16 PM)
Line 1: Line 1:
 += 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
 +