[sql] In Postgresql, force unique on combination of two columns

I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both.

For instance:

CREATE TABLE someTable (
    id int PRIMARY KEY AUTOINCREMENT,
    col1 int NOT NULL,
    col2 int NOT NULL
)

So, col1 and col2 can repeat, but not at the same time. So, this would be allowed (Not including the id)

1 1
1 2
2 1
2 2

but not this:

1 1
1 2
1 1 -- would reject this insert for violating constraints

Seems like regular UNIQUE CONSTRAINT :)

CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c));

More here

If, like me, you landed here with:

• a pre-existing table,
• to which you need to add a new column, and
• also need to add a new unique constraint on the new column as well as an old one, AND
• be able to undo it all (i.e. write a down migration)

Here is what worked for me, utilizing one of the above answers and expanding it:

-- up

ALTER TABLE myoldtable ADD COLUMN newcolumn TEXT;
ALTER TABLE myoldtable ADD CONSTRAINT myoldtable_oldcolumn_newcolumn_key UNIQUE (oldcolumn, newcolumn);

---

ALTER TABLE myoldtable DROP CONSTRAINT myoldtable_oldcolumn_newcolumn_key;
ALTER TABLE myoldtable DROP COLUMN newcolumn;

-- down

Create unique constraint that two numbers together CANNOT together be repeated:

ALTER TABLE someTable
ADD UNIQUE (col1, col2)