Oracle nls_sort equivalent in postgres

Question

Working in migration of DB from Oracle to Postgres. I'm trying to achieve the sorting behavior in my application, where alphanumeric label starting with digits need to come after letters (explained in below example).

I tried the french collation in Postgres, but couldn't achieve the same result which I got in Oracle using NLS_SORT "french".

Posting Oracle queries, I need an equivalent in Postgres which can be set in DB once so that application uses it afterwards.

Oracle:

ALTER SESSION SET NLS_SORT='FRENCH';
ALTER SESSION SET NLS_COMP=LINGUISTIC;
select LABEL from CARBON order by LABEL ;

 LABEL
    APPLE
    BALL
    102C
    108C
    108D

Postgres:

I tried by creating collation named french from the pg_collation entry fr-FR-x-icu and alter the existing table column using the pgAdmin GUI like this:

label character varying(255) COLLATE public.french NOT NULL

No luck, I am getting the same result.

select LABEL from CARBON order by LABEL ;

LABEL
102C
108C
108D
APPLE
BALL

I tried the below query also in Postgres , it is giving the same result as above. Am I missing something in Postgres?

select LABEL from CARBON order by LABEL collate "fr_FR"

Answer 1

You can easily solve that by creating a custom ICU collation (available from PostgreSQL v10 on) that sorts digits greater than Latin characters:

CREATE COLLATION weird (
   LOCALE = 'fr-u-kr-latn-digit',
   PROVIDER = 'icu'
);

ALTER TABLE carbon ALTER label TYPE text COLLATE weird;

SELECT * FROM carbon ORDER BY label COLLATE weird;

 label 
-------
 APPLE
 BALL
 102C
 108C
 108D
(5 rows)

This assumes a recent ICU library, old versions have a somewhat different syntax.

With older versions of the ICU library, you may have to use

CREATE COLLATION digitslast (
   PROVIDER = 'icu',
   LOCALE = 'fr@colReorder=latn-digit'
);