Mysterious inequality behavior when comparing date strings

Question

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.

Answer 1

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;