skip to Main Content

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

2

Answers


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

    Login or Signup to reply.
  2. 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;
      
    3. Use a custom unique index instead of the primary key:

      CREATE UNIQUE INDEX ON public.languages (lower(code));
      
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search