Postgresql icu collation

1

Hi. I have some data in my database that is a string representation of integers (mostly, there are some alphabetic characters here and there). I want to query this field using postgres’ collation, specifically I want to use

create collation numeric (provider = icu, locale = 'en-u-kn-true');
select '20' between '10' and '100' collate numeric;

and I expect to get true. But I get false instead. This seems to contradict the postgres documentation

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

Numeric ordering, sorts sequences of digits by their numeric value, for example: A-21 < A-123 (also known as natural sort).

It seems that icu support is present because I can see icu entries when I run select * from pg_collation;

Running the same code locally and within github actions works. Render seems to be the odd man out, but I’m open to the possibility that I’m missing something.

2

Here’s a minimal reproduction that works on all the other postgres databases I’ve tried so far:

=> create collation numeric (provider = icu, locale = 'en-u-kn-true');
CREATE COLLATION
=> select '20' between '10' and '100';
┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)

=> select '20' between '10' and '100' collate numeric;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

On render’s DB, the final query returns false.

4

Hi @PaulOstazeski! Thanks for the detailed description and repro steps here! I was able to replicate the behavior on my end as well. It seems that the PostgreSQL image is using an older libicu version, which doesn’t support specifying that locale. We do expect this to be addressed once we roll out full support for PostgreSQL 12/13, which is currently in progress (early access now).

1 Like
5

Great! I’ll email support and see about getting early access.