CODEWITHSUNDEEP
sqlpostgresqlpostgresql-13
Romillion
Romillion

Reputation: 159

Case sensitive column in PostgreSQL

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

enter image description here

Upvotes: 0

Views: 93

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247625

You have got a few choices:

  1. Use the citext extension:

    CREATE EXTENSION IF NOT EXISTS citext SCHEMA public;
ALTER TABLE public.languages ALTER code TYPE citext;

  2. Use a case insensitive ICU collation:

    CREATE COLLATION und_ci (
   PROVIDER = icu,
   DETERMINISTIC = FALSE,
   LOCALE = 'und@colStrength=secondary'
);

ALTER TABLE public.languages ALTER code TYPE character varying(10) COLLATE und_ci;

    See the ICU documentation for how to define ICU collations.

  3. Use a custom unique index instead of the primary key:

    CREATE UNIQUE INDEX ON public.languages (lower(code));

Upvotes: 2

Mahdi Zarei
Mahdi Zarei

Reputation: 221

There is no default case-insensitive collation for postgress but you can use extensions like https://www.postgresql.org/docs/current/citext.html

Upvotes: 0

Related Questions