Last Updated: 27 Jun 2023
|
Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
backend-tech:sql:sql-notes [May 15, 2008 06:05 AM] dordal |
backend-tech:sql:sql-notes [Jun 25, 2023 06:16 PM] 111.225.148.140 removed |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | = SQL Notes, Tips & Tricks = | ||
+ | |||
+ | * typecasting is done in SQL via double-colon :: (e.g var:: | ||
+ | * 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, | ||
+ | * NULLIF() does the opposite... if you say NULLIF(myColumn, | ||
+ | * 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 (' | ||
+ | * Pattern matching: there is always the nasty LIKE and NOT LIKE, but Postgres supports regular expressions too | ||
+ | |||