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

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.

CodePudding user response：

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}`
  }