search

supabase / postgres

Unmodified Postgres with some useful plugins
https://supabase.com
PostgreSQL License
1.39k stars 139 forks source link

Text collation not supported in SB postgres as some locales are not generated/collations are missing #1133

Closed nk9 closed 2 months ago

nk9 commented 2 months ago

Bug report

Describe the bug

I have a text column which contains strings like "1", "10", "5B", "5A", etc. I have set a collation on the column like this:

ALTER TABLE test ALTER COLUMN name type TEXT COLLATE numeric;

When I order by this column locally, it now works as expected. With the above data, the previous sort was 1, 10, 5A, 5B; with the collation set on the column locally, it now returns 1, 5A, 5B, 10. So far so good.

I added this collation to two columns in two different tables. But when I created a migration, only one of the tables was altered according to the migration file. I found this odd, but whatever. (The sorting behavior on the columns in both tables had changed.) So I updated the migration for both columns to use the "numeric" collation. I pushed the change, with no issues.

However, when I now ORDER BY on the production server, the sort order hasn't changed. What gives?

To Reproduce

Run this code on a local supabase instance, and on a remote (SB-hosted) instance:

CREATE TABLE test (name text);
INSERT INTO test (name) VALUES (unnest(array['1', '10', '5B', '5A']));
ALTER TABLE test ALTER COLUMN name type TEXT COLLATE numeric;
SELECT * FROM test ORDER BY name;

Note that the numeric collation is available by default, and so doesn't need to be created explicitly.

Expected behavior

On the self-hosted instance, you'll get: 1, 5A, 5B, 10 On the remote instance, you'll instad get: 1, 10, 5A, 5B

Obviously, the self-hosted version is the correct one. Also, what's up with the migration failing to notice the change in column collation?

Screenshots

Not needed.

System information

Additional context

Add any other context about the problem here.

encima commented 2 months ago

Hi @nk9 Thanks for opening. I assume you are running Supabase by docker-compose and not the CLI, is that right?

If you run select * from pg_collation; on hosted supabase then you will see that numeric is not a type there (and likely why the migration failed).

Running locally seems like it is luck that your pg_collation table recognises numeric and has the locales generated. Running the same on the latest version of the CLI does not provide that collation.

For the collation itself, Postgres has docs on creating custom collations which will allow you to create a numeric collation from an existing locale. I.e.

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
ALTER TABLE test ALTER COLUMN name type TEXT COLLATE numeric;

This should resolve your issue but I will amend this issue description and move to the Supabase Postgres repo to let the team respond and decide on a path forward if one is needed

nk9 commented 2 months ago

Thanks for the reply. I'm quite confused now, though. I am using supabase start locally. And when I connect to either DB with my SQL client (both local and remote), I see the numeric collation. I also see it on supabase.com using the SQL Editor.

image

Is there perhaps another variable which would explain why I can see this collation, and yet why it isn't working?

nk9 commented 2 months ago

Oh, interesting. So the local collation has a colliculocale of en@colNumeric=yes, whereas the remote collation simply has None. So that's why it's not working.

I didn't think I'd added this remote collation, but I looked in my migration history and I did indeed add it myself a few months back. So I should just need to update it. I had thought this was built into Supabase for some reason.

Thanks for helping me figure this out. I guess this isn't a Supabase bug after all, so feel free to close.