Query failed: collation "numerickn" for encoding "UTF8" does not exist

Question

I have a column (vendor_name) in Postgresql (AWS RDS) table which can contain alphanumeric values. I would like to do a natural sort on this column.

The sample data in the table is as follows

delta 20221120
delta 20220109
costco delivery 564
costco delivery 561
united 01672519702943
Uber

I have created a colllate in the db as below.

CREATE COLLATION IF NOT EXISTS numerickn (provider = icu, locale = 'en-u-kn-true')

If anyone sorts on the vendor name column in the UI grid, I am adding the following clause dynamically in my query.

ORDER BY "vendor" COLLATE "numerickn"

However, it gives the following error, though I see collation exists in DB.

Error: Query failed: collation "numerickn" for encoding "UTF8" does not exist

I am not sure why it does not work if collate exists in the DB. In my vendor name, numeric can appear anywhere within the string, so there is no pattern.

try with a collation that you know works? – May 02 '22 at 07:41 — , May 02 '22 at 07:41
do you get any error message when you create numerickn? – May 02 '22 at 07:42 — , May 02 '22 at 07:42
simple question is int numerickn or numericn? – May 02 '22 at 07:42 — , May 02 '22 at 07:42
No, we did not get any errors while creating the collation. – user2026504 May 03 '22 at 02:20 — user2026504, May 03 '22 at 02:20

score 0 · Accepted Answer · answered May 03 '22 at 02:29

I could not find why it was not working in the stage environment while in my local it was working.

In the end, I moved away from colation logic and implement the natural sort in a different way found in stack overflow only.

PostgreSQL ORDER BY issue - natural sort

I am using Nodejs in my api code. My solution goes as follows

qOrderBy = String.raw` ORDER BY  ARRAY(
        SELECT ROW(
            CAST(COALESCE(NULLIF(match[1], ''), '9223372036854775807') AS BIGINT),
            match[2]
          )
          FROM REGEXP_MATCHES(vendor, '(\d*)|(\D*)', 'g')
          AS match ) ${sortOrder}`
  }

Query failed: collation "numerickn" for encoding "UTF8" does not exist

1 Answers1