Question 264479 in PostgreSQL Question posted in
The official documentation can be found here.

Accent sensitive sort in PostgreSQL

How to sort text in PostgreSQL in Latvian alphabetic order, so any letter without accent always comes before the letter with accent?

For example this

select * from (
    select 'bā' as name
    union select 'āa'
    union select 'ac'
    union select 'Āb'
    union select 'Ā'
) t
order by name COLLATE "lv-x-icu"

returns Ā āa Āb ac bā

but the expected result is ac Ā āa Āb bā

I have tried to create some custom collations but none of them returned the expected result.

Tags:

2

Answers


  1. 0

    You can use CASE in ORDER BY, and regular expression for "not ASCII", so the row with only ASCII characters ‘ac’ appears first (sorted by 0).the rows with non-ASCII characters appear last (sorted by 1).

    select * from (
    select 'bā' as name
    union select 'āa'
    union select 'ac'
    union select 'Āb'
    union select 'Ā'
) t
order by case when name ~ '[^[:ascii:]]' then 1 else 0 end

    Demo here

    Login or Signup to reply.
  2. 0

    If you have PostgreSQL built with ICU support, you can create a collation that sorts upper case letters first:

    CREATE COLLATION latuvian_upper_first (
   PROVIDER = icu,
   LOCALE = 'lv@colCaseFirst=upper'
);
NOTICE:  using standard form "lv-u-kf-upper" for ICU locale "lv@colCaseFirst=upper"

select * from (
    select 'bā' as name
    union select 'āa'
    union select 'ac'
    union select 'Āb'
    union select 'Ā'
) t
order by name COLLATE latuvian_upper_first;

 name 
══════
 Ā
 āa
 Āb
 ac
 bā
(5 rows)

    The notice show you the new syntax. I used the old syntax, in case you are using an old ICU library.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search