CODEWITHSUNDEEP
sqlstringpostgresqlherokucomparison
Anh Nguyen
Anh Nguyen

Reputation: 1234

String comparison in SQL query on Heroku Postgres

I saw the weird behaviour of Heroku Postgres yesterday. When I executed this sql query for string comparison:

SELECT ('2013/12-25' <= '2013/12/')

Postgres on my development machine (9.2.3, Mac OS X Mavericks) returned true (which is correct) while Postgres on Heroku (9.2.4) returned false!!!

Does anyone know how to fix this?

Upvotes: 1

Views: 193

Answers (1)

valex
valex

Reputation: 24144

It seems that these two instances of Postgresql [have different default COLLATIONS (http://www.postgresql.org/docs/9.3/static/collation.html) in the server configuration:

Try to check if this command outputs the same result on both servers:

show lc_collate

For your example different collations can show you different results. For instance:

SELECT (('2013/12-25' COLLATE "en_US") <= ('2013/12/' COLLATE "en_US"))
UNION ALL
SELECT (('2013/12-25' COLLATE "ko_KR") <= ('2013/12/' COLLATE "ko_KR"))

SQLFiddle demo

Upvotes: 1

Related Questions