I use PostgreSQL 13.15 version and i don’t understand how to make column case insensitive.
This is table languages and column code is primary key. I want to make it case insensitive.
CREATE TABLE IF NOT EXISTS public.languages
(
code character varying(10) COLLATE pg_catalog."default" NOT NULL,
name character varying(100) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT languages_pkey PRIMARY KEY (code)
);
I want to prevent second insert because i already have a code ‘eng’
INSERT INTO public.languages(code, name) VALUES ('eng', 'English');
INSERT INTO public.languages(code, name) VALUES ('ENG', 'English');
Currently it successfully added second insert and it’s wrong because i already have code ‘eng’ and i don’t want to have duplications ‘ENG’ or ‘Eng’ . If i’m not mistaking it can be done by using Collation but i’m not sure if it’s a correct way. In pgAdmin 4 i didn’t find anything in properties on column
2
Answers
There is no default case-insensitive collation for postgress but you can use extensions like https://www.postgresql.org/docs/current/citext.html
You have got a few choices:
Use the
citext
extension:Use a case insensitive ICU collation:
Use a custom unique index instead of the primary key: