In Postgresql, force unique on combination of two columns

Question

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

autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10).

If col1 and col2 make a unique and can't be null then they make a good primary key:

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

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

ALTER TABLE someTable
ADD UNIQUE (col1, col2)

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