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:
- 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.
- The results are wrong for the
<
and>=
operators when the dates are the same but in different formats. This seems like a bug.