CODEWITHSUNDEEP
postgresqlcollationuppercase
user2495072
user2495072

Reputation: 1

Force Postgres shift to uppercase rather than lowercase before sorting case insensitive?

I try to migrate to postgres from pervasive. In pervasive there was something like 'upper.alt' - alternative collation. I don't really know how it works, but I have to make my new postgres database to behave like pervasive with this collation.

I use Postgres 9.2.4 and utf-8 encoding and LC_COLLATE='Polish_Poland.1250' .

Upvotes: 0

Views: 1017

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658102

You can try and order with COLLATE "C". That would get what you want in your example. It has side effects though! Effectively everything is ordered according to the byte values of the encoded character.

WITH x(col) AS (
    VALUES
     ('ABC_AAAAA')
    ,('ABC_BBBBB')
    ,('ABC_ZZZZZ')
    ,('ABCAAAAA')
    ,('ABCBBBBB')
    ,('ABCZZZZZ')
    )
SELECT *
FROM   x
ORDER  BY col COLLATE "C"

This option to change the collation for individual expressions (as opposed to using a collation defined at creation time of the db) was introduced with Postgres 9.1.
More about collation in the manual here.

Upvotes: 1

Related Questions