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.

Answer 1

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

Answer 2

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.