Create a unique constraint with CHECK in PostgreSQL

Question

Consider that there is the following table:

create table table_1(
    id                 serial
                       PRIMARY KEY,
    col1               varchar(50),
    col2               varchar(50),
    status             varchar(1)  -- A=active P=pending D=Deleted
);

now what I want is to create a unique constraint on (col1,col2) but it should not consider those with status ='D' i.e Consider there is 2 rows in the table:

INSERT INTO table_1(col1,col2,status) VALUES ('row1', 'row1', 'A');
INSERT INTO table_1(col1,col2,status) VALUES ('row2', 'row2', 'D');

Then I should NOT be able to add the following row:

INSERT INTO table_1(col1,col2,status) VALUES ('row1', 'row1', 'A');

But I SHOULD be able to add the following row:

INSERT INTO table_1(col1,col2,status) VALUES ('row2', 'row2', 'A');

Gordon Linoff · Accepted Answer · 2018-04-03 15:53:12Z

1

You can do this with a partial unique index:

create unique index id_table1_col1_col2_status on table_1(col1, col2, status)
    where status <> 'D';

Here is a SQL Fiddle that you can use to see it work.

answered Apr 3, 2018 at 15:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Said Saifi Over a year ago

Thank you. I just need this: create unique index id_table1_col1_col2 on table_1(col1, col2) where status <> 'D'; I did not know we could use WHERE when creating an index.

Kjetil S. Over a year ago

How will status <> 'D' work if status is null? If it's never going to be null, then it should be created with the not null constraint.

Collectives™ on Stack Overflow

Create a unique constraint with CHECK in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related