Search code examples
postgresqloracle-databasecharacter-encodingcharacter-set

Need some clarification about LC_COLLATE and LC_CTYPE

I have gone through the official postgres documentation to know about the LC_COLLATE and LC_TYPE. But, still I don't understand it correctly.

Can anyone help me in understanding these concepts and impact of these, especially when we are trying to load data which is at oracle of encoding WE8ISO8859P15 and at postgres encoding is as utf-8 and collation/ctype is en_US.UTF-8.

Thanks in advance

Solution

  • This is part of the “locale”, the national language support, which is different from the encoding (but the locale has to belong to the encoding).

    LC_CTYPE determines character classification (which characters are letters, numbers, space characters, punctuation etc.) as well as case conversion. Different languages have different ideas about that.

    LC_COLLATE determines how strings are compared and sorted.

    The first has little impact on the behavior of PostgreSQL, but the second is very relevant: it determines how b-tree indexes on string columns are ordered (which is why it cannot be changed after a database has been created) and how ORDER BY sorts strings by default (which is directly user-visible).