Postgres unique constraint across two columns?

Question

Here's my current database structure:

Col1   Col2
 1    abc123
 1    abc123
 1    def321
 2    xyz789
 2    xyz789

I'd like to ensure that any entry in Col2 is only used for the same entry in Col1. For example, the database would let you add this row:

Col1   Col2
 2    lmn456

But not this one:

Col1   Col2
 2    abc123

Is there a way to enforce this with a unique index? Normally, the unique enforces that the specific combination is unique for the entire table (i.e. (1,abc123) shows up no more than once).

I can't move Col2 to a different table and use a join because I need to support multiple values of Col2 for each entry in Col1 - it's not 1-to-1.

Why would you want/need to keep duplicates? Gordon wasn't totally wrong when he said you probably have a modeling issue. — Simon Berthiaume
– Simon Berthiaume, Commented Feb 9, 2018 at 0:55

klin · Accepted Answer · 2018-02-09 02:55:32Z

5

This is a typical case for using an exclude constraint.

The constraint will use btree operators <> and =, hence you have to install btree_gist extension.

create extension if not exists btree_gist;

create table my_table(
    col1 int, 
    col2 text,
    exclude using gist (col1 with <>, col2 with =)
);

Test:

insert into my_table values
    (1, 'abc123'),
    (1, 'abc123'),
    (1, 'def321'),
    (2, 'xyz789'),
    (2, 'xyz789')
returning *;

 col1 |  col2  
------+--------
    1 | abc123
    1 | abc123
    1 | def321
    2 | xyz789
    2 | xyz789
(5 rows)    

insert into my_table 
values (2, 'abc123');

ERROR:  conflicting key value violates exclusion constraint "my_table_col1_col2_excl"
DETAIL:  Key (col1, col2)=(2, abc123) conflicts with existing key (col1, col2)=(1, abc123).

answered Feb 9, 2018 at 2:55

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

gwintrob Over a year ago

Thanks. The "cage at a zoo" example in that doc is exactly what I'm looking for.

Gordon Linoff · Accepted Answer · 2018-02-08 19:24:27Z

5

No. You have the wrong data structure.

You should be storing col2 in a separate table one row per col1 and col2. Then you can lookup the value using a join.

answered Feb 8, 2018 at 19:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

gwintrob Over a year ago

Thanks for the idea! This won't work because I want to have multiple values of Col2 for the same value in Col1. I'll update the example to make this clear.

Gordon Linoff Over a year ago

@gwintrob . . . I don't understand your comment at all. These would just be stored in different rows.

gwintrob Over a year ago

Unrelated comment. It's pretty cool that StackOverflow brings together 2 Gordons who both graduated from MIT :) Can you explain more about how the join would work? I thought you were suggesting that there would be unique constraint on col2 in the separate table.

Collectives™ on Stack Overflow

Postgres unique constraint across two columns?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related