Useful for debugging sites that are hogging connections, especially if you have persistent connections on.

SELECT
    datname,
    COUNT(*) AS open_connections,
    MAX(backend_start) AS oldest_connection,
    MIN(backend_start) AS newest_connection
    FROM pg_stat_activity GROUP BY datname
UNION SELECT
    'Summary',
    COUNT(*),
    MAX(backend_start),
    MIN(backend_start)
    FROM pg_stat_activity;

Sorry, I accidentally deleted this post. Here it is again.

There seems to be a bug in the way PostgreSQL 8.1 handles ORDER BY when you format a timestamp. An example is:

SELECT to_char(date, ‘MM-DD-YYYY’) AS date FROM table ORDER BY date ASC;

In this case, you can get bad results, as 1-1-2009 will be placed before 3-3-2008. This doesn’t appear to affect versions after 8.1. The workaround is to specify the table name before the date, ie:

SELECT to_char(date, ‘MM-DD-YYYY’) AS date FROM table ORDER BY table.date ASC;

UPDATE: It appears that PostgreSQL 8.1 actually handles it correctly, and 8.2 handles it improperly. Also note that ORDER BY works the same in 8.3 as it does in 8.1.

Comcast quietly rolled out new HD channels in Tacoma and Seattle. The new channels are:

• Animal Planet HD
• TLC HD
• Discovery HD
• Food HD
• SciFi HD
• HGTV HD

Unfortunately, they’re still way behind DirectTV.

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)