UNACCENT when checking for UNIQUE contraint violations in PostgreSQL

Question

We have a UNIQUE constraint on a table to prevent our city_name and state_id combinations from being duplicated. The problem we have found is that accents circumvent this.

Example: "Montréal" "Quebec" and "Montreal" "Quebec"

We need a way to have the unique constraint run UNACCENT() and preferably wrap it in LOWER() as well for good measure. Is this possible?

Probably the simplest way is to have two columns: first just to display which contains values like `"Montréal"` and second one for checks, indexing, searching, filtering etc which contains values like `"montreal"`... — Abelisto, Jul 01 '20 at 07:41

score 1 · Answer 1 · answered Jul 01 '20 at 07:23

You can create an immutable version of unaccent:

CREATE FUNCTION noaccent(text) RETURNS text
   LANGUAGE sql IMMUTABLE STRICT AS
'SELECT unaccent(lower($1))';

and use that in a unique index on the column.

An alternative is to use a BEGORE INSERT OR UPDATE trigger that fills a new column with the unaccented value and put a unique constraint on that column.

score 0 · Answer 2 · answered Jun 30 '20 at 16:44

0

You can create unique indexes on expressions, see the Postgres manual:

https://www.postgresql.org/docs/9.3/indexes-expressional.html

So in your case it could be something like

CREATE UNIQUE INDEX idx_foo ON my_table ( UNACCENT(LOWER(city_name)), state_id )

answered Jun 30 '20 at 16:44

Cody Caughlan

32,456
5
63
68

1

Should then also note that any queries wishing to make use of that index also have to filter on `unaccent(lower(city_name))` rather than just `city_name` – Bill Jetzer Jun 30 '20 at 16:53
What about using something like a check instead of an index? – Matt Weber Jun 30 '20 at 16:54
It's worth noting this will not be a heavy write table due to it's nature, mostly read. – Matt Weber Jun 30 '20 at 16:55
2

Isn't unaccent only stable, meaning it can't be directly used in an index function? – JGH Jun 30 '20 at 17:37

UNACCENT when checking for UNIQUE contraint violations in PostgreSQL

2 Answers2