1

Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line.

mydb=> select '20150526' > '2015-05-25';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' > '2015-05-26';
-[ RECORD 1 ]
?column? | f

mydb=> select '20150526' > '2015-05-27';
-[ RECORD 1 ]
?column? | f

If Postgres was actually comparing the strings directly, either all three would be true or false, depending on how character ordering is defined. This seems to establish that Postgres is trying to convert the strings to dates before comparison, presumably based on some heuristic about the formats. However:

mydb=> select '20150526' < '2015-05-27';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-26';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-25';
-[ RECORD 1 ]
?column? | f

Note that the result of the middle query is incorrect, if interpreted heuristically as dates! The inverse issue occurs with the >= operator. This caused an extremely subtle bug in a trigger function I'm using.

So, two mysteries:

  1. Postgres seems to be heuristically interpreting these strings as dates in order to do comparisons. This is a little surprising, so I would like confirmation.
  2. The results are wrong for the < and >= operators when the dates are the same but in different formats. This seems like a bug.

1 Answer 1

8

What makes you think you are comparing dates?

Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text:

SELECT '20150526' > '2015-05-26' AS text2text
     , '20150526'::date > '2015-05-26'::date AS date2date;

text2text | date2date
----------+----------
 t        | f

Try:

SELECT pg_typeof(col) FROM (VALUES ('20150527')) t(col);  -- no cast

pg_typeof
---------
text

The result of your comparison of text values depends on your locale, on COLLATION rules to be precise. To see the default COLLATION of your session:

SHOW lc_collate;

COLLATION can be also be set per column or even per expression. To see the actual COLLATION for any expression use the collation for (any) construct:

SELECT collation for (my_column) FROM tbl LIMIT 1;

To test how strings are sorted in your locale:

SELECT *
FROM  (
   VALUES
     ('20150526')
   , ('2015-05-25')
   , ('20150525')
   , ('2015-05-26')
   , ('20150527')
   , ('2015-05-27')
   ) t(col)
ORDER BY col;    -- COLLATE "C"

Compare this to the sort order by pure byte-value by appending COLLATE "C" to ORDER BY. To see the result of your comparison with a different COLLATION:

SELECT '20150526' > '2015-05-26' COLLATE "C" AS text2text_c;
2
  • Thank you! To extend this answer a bit, my database defaults to en_US.UTF-8 and the default sorting rule ignores punctuation. Apparently this descends from Unix behavior (see here). May 28, 2015 at 5:27
  • Correction: it's not correct for me to say that it ignores punctuation. More like it uses punctuation as tiebreakers. Not sure if that's a 100% correct statement either... May 28, 2015 at 5:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.