I'm running Postgresql 9.6 on Ubuntu 16.04. The collation order is en_ZA.UTF-8. I'm puzzled by how Postgres compares strings:

test=> select 'b' > 'B';

test=> select 'ba' > 'B';

test=> select 'b' = 'B';

test=> select 'ba' > 'C';

The response to the second query doesn't make sense to me.


That's just how your locale defines sort order. Obviously upper case letters act as tiebreakers if the string is otherwise identical - then they sort after lower case equivalents. But 'ba' still sorts after 'B' (and 'BA' sorts after 'b').

Compare to results without collation rules:

SELECT   'b' > 'B'
      , 'ba' > 'B'
      , 'ba' > 'C'
      , 'b'  > 'B' COLLATE "C"
      , 'ba' > 'B' COLLATE "C"
      , 'ba' > 'C' COLLATE "C";
 ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
 f        | t        | f        | t        | t        | t

(My current collation setting German_Germany.1252 happens to behave just like your en_ZA.UTF-8.)

|improve this answer|||||
  • Erwin, thanks a lot for your comment! Are you aware of a reference that rigorously defines this sorting order -- I'm still not 100% sure how it's supposed to work. – dmitry Feb 28 '17 at 15:56
  • @dmitry: You might investigate the locale definitions of your OS, which provides the rules Postgres uses. – Erwin Brandstetter Feb 28 '17 at 15:59
  • @dmitry it means it's supposed to work: First order lexicographically (case insensitive) and then, only in case of a tie, order using the cases of the characters. – ypercubeᵀᴹ Feb 28 '17 at 16:31
  • @ypercubeᵀᴹ That makes perfect sense:-) – dmitry Feb 28 '17 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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