« Comcast Rolls Out New HD Channels
Neat Trick: Stats For Open PostgreSQL Connections »

PostgreSQL 8.1 ORDER BY bug

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.

This entry was posted on Thursday, March 6th, 2008 at 12:46 pm and is filed under PostgreSQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “PostgreSQL 8.1 ORDER BY bug”

  1. bill Says:
    March 6th, 2008 at 1:48 pm

    This is not a bug but a miss understanding …
    You are, in first case, making a query using the string result (to_char) so in this case 1-1-2009 is before 3-3-2008 but 1-2-2007 will be before also !
    In the other case you are using the date field value for the query so in date format you will get the good result …
    Make simply the same test using to_char(YYYY-MM-DD’) and all will be find!

    Regards,

  2. admin Says:
    March 6th, 2008 at 5:51 pm

    Bill,

    You are right in saying it’s being ordered as a string, however, it’s still a bug, since PostgreSQL 8.2 is affected by it.

    It does appear that 8.1 is handling it properly. Also, 8.3 handles it the same way 8.1 does, so it looks like it’s actually an 8.2 bug

    In any case, the workaround provides a query that produces consistent results across all 8.x versions of PostgreSQL.

Leave a Reply