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

Is Postgresql’s Unique Constraint used to group similar values?

Let’s say I have a database with columns name, last_name and type

They’re all strings, though type can only be TYPE_A, TYPE_B or TYPE_C

I want a row in the database to be Unique when name and last_name already exist.

Additionally, I want TYPE_A and TYPE_B to be treated as the same in uniqueness and TYPE C to be treated differently. Essentially, I want either TYPE_A or TYPE_B to be seen as TYPE_A_B. I don’t want to modify any values already present in the database.

I only want to add this constraint to an already existing database, how do I do this?

(I’m using Postgres)

For example: (name, last_name, type)

Valid:

John, Doe Type A

John, Doe Type C

Invalid (Unique constraint violation)

John, Cena Type C

John, Doe Type A <-

John, Doe Type B <-

Tags:

2

Answers


  1. 0

    Postgres’ constraints do not support expressions, but you can achieve the same goal with a unique index:

    create table mytable (name text, last_name text, type text);

create unique index myidx on mytable (
    name, 
    last_name, 
    ( case when type in ('Type A', 'Type B') then 'Type AB' else type end )
);

    fiddle

    insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type A');
-- ok

insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type C');
-- ok

insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type B');
ERROR:  duplicate key value violates unique constraint "myidx"
DETAIL:  Key (name, last_name, (
CASE
    WHEN type = ANY (ARRAY['Type A'::text, 'Type B'::text]) THEN 'Type AB'::text
    ELSE type
END))=(John, Doe, Type AB) already exists.

    If you wanted to enforce unicity on types A/B only, ignoring other types, then you would use a filtering unique index instead:

    create unique index myidx 
    on mytable (name, last_name)
    where(type in ('Type A', 'Type B'));
    Login or Signup to reply.
  2. 0

    Create a functional unique index:

    create unique index idx on mytable 
(
  name,
  last_name,
  (case when type in ('Type A', 'Type B') then 'TYPE_A_B' else type end)
);

    (The additional parentheses around the CASE expression are mandatory here.)

    Demo: https://dbfiddle.uk/rIzYyU-n

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search