PostgreSQL Tips Of The Day
1. You can do UPDATE queries, using syntax similar to INSERT:
UPDATE table SET (column1, column2) = (’foo’, ‘bar’);
This can make your code much easier to manage when creating queries in PHP:
$vals = array(’column1′ => $db->quote(’foo’), ‘column2′ => $db->quote(’bar’));
$db->query(sprintf(’UPDATE table SET (%s) = (%s) WHERE id = 1′),
join(’, ‘, array_keys($vals)), join(’, ‘, $vals));
2. Timestamps, intervals, and dates can easily be formatted using the to_char function:
SELECT to_char(NOW(), ‘MM-DD-YYYY’);
to_char
————
03-02-2008
(1 row)
3. You can add and subtract with date/time data types:
SELECT NOW() - ‘10 minutes’::interval;
?column?
——————————-
2008-03-02 10:20:32.849205-08
(1 row)SELECT current_date - ‘1 week’::interval;
?column?
———————
2008-02-24 00:00:00
(1 row)
4. The age function comes in very handy. It’s used to calculate the age between intervals, timestamps, and dates:
SELECT age(NOW(), NOW() - ‘10 minutes’::interval);
age
———-
00:10:00
(1 row)
5. You can achieve IF/ELSE-like functionality using CASE WHEN/ELSE/END:
SELECT CASE WHEN age(NOW(), NOW() - ‘17 years’::interval) >= 18 THEN ‘you are older than 18′ ELSE ‘you are younger than 18′ END;
case
————————-
you are younger than 18
(1 row)