postgresql - case insensitive uniqueness - how to update against the current record, regardless of case?

Question

I am using Hasura on top of Postgres to store companies and other information. The source data has the same spelling of records, but in differing cases - for example:

name	country
Reckitt Benckiser (Aust) Pty Ltd	US
RECKITT BENCKISER (AUST) PTY LTD	AU

question from:https://stackoverflow.com/questions/65660279/case-insensitive-uniqueness-how-to-update-against-the-current-record-regardle

Answer 1

You didn't specify what version of Postgres you are running, but if it is 12 or above you can add a virtual (generated) column, then put a unique constraint on that column. See example:

alter table table_name add v_company_name text generated always as ( lower(company_name) stored);
alter table table_name add constraint company_name_bk unique (v_company_name);