4

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?

My table definition:

CREATE TABLE mytable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
)
7

This unicity constraint can be enforced with this unique index:

CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2));

Demo:

test=> insert into mytable (col1,col2) values(1,2);
INSERT 0 1
test=> insert into mytable (col1,col2) values(2,1);
ERROR:  duplicate key value violates unique constraint "idxname"
DETAIL:  Key ((LEAST(col1, col2)), (GREATEST(col1, col2)))=(1, 2) already exists.
  • Very smart, indeed. – Renzo Jun 28 '15 at 16:48
  • I am constantly in awe of PostgreSQL - I'll be using it in future. This is a really nice answer and pretty cool! I thought that you'd have to jump through all sorts of hoops for this one! Am I right in thinking that not even Oracle has this capability? +1. – Vérace Jun 28 '15 at 20:49
  • @Vérace: While I obviously agree with you on Postgres, Oracle also has "function-based indexes". They have trouble with NULL values, though (which they did not implement properly): Oracle Database will not use the index unless the query filters out nulls – Erwin Brandstetter Jun 28 '15 at 23:39
  • @ErwinBrandstetter - how does PostgreSQL cope with NULLs in this scenario? – Vérace Jun 29 '15 at 0:09
  • @Vérace: NULL values are indexed like any other value. Not relevant for this question since both columns are defined NOT NULL. More: dba.stackexchange.com/q/9759/3684. If NULL was allowed for this special case, there would be more considerations since GREATEST and LEAST both prioritize non-null values over NULL ... – Erwin Brandstetter Jun 29 '15 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.