PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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. 0

    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