Search code examples
postgresqlgraphqlhasura

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

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

I have an index on the name of the company:

CREATE UNIQUE INDEX name_company_unique_idx on company (LOWER(name));

When I "upsert" the second record, it throws a uniqueness error, so the index is working correctly. However what I want is for the second record to realise the first record is the same thing, and update the country to AU. If I don't have the index, I get two records for the same entity, in differing cases.

I don't want to store everything in upper or lower case, but am happy for it to take whatever case the first record that was inserted is.

How can I make the upsert process case agnostic?

Solution

  • 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);