SQL Notes, Tips & Tricks [StartupCTO]

Last Updated: 27 Jun 2023

|

Author: dordal

Page locked

This page is currently locked for editing by another user. You have to wait until this user finishes editing or the lock expires.

Currently locked by: 18.117.81.240
Lock expires at: Apr 20, 2024 05:49 PM (179 min)

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

= 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

Page locked

About Me

Recent Changes

Get In Touch

Follow