non UNIQUE constraints

So, I got in this situation: I create a table (let say model_a), in this table I have 2 cols (let say col1 and col2). I need a constraint that satisfy this reality:

model_a

col1 | col2
-----+-----
  1  |  1  //first row
  2  |  1  >> ok
  1  |  1  >> ok
  1  |  2  >> can not insert this row

It’s not UNIQUE constraint, you can duplicate the first row.

The only one time this constraint active is col1 is the same but col2 is different.

Need help 🙂 Thanks a lot!

Answer

I am thinking of a unique index on the least/greatest value of both columns, that applies only to rows where the values are not equal:

create unique index myidx 
    on model_a (least(col1, col2), greatest(col1, col2))
    where (col1 <> col2)

Demo on DB Fiddle:

insert into model_a (col1, col2) values (1, 1); -- ok
insert into model_a (col1, col2) values (2, 1); -- ok
insert into model_a (col1, col2) values (1, 1); -- ok

insert into model_a (col1, col2) values (1, 2);
-- ERROR:  duplicate key value violates unique constraint "myidx"
-- DETAIL:  Key (LEAST(col1, col2), GREATEST(col1, col2))=(1, 2) already exists.