In Postgresql, force unique on combination of two columns

sql

migrations

error-handling

constraints

byAlex Kataev·Feb 23, 2025

To enforce a unique combination of two columns in PostgreSQL, apply a UNIQUE CONSTRAINT:

ALTER TABLE your_table ADD CONSTRAINT unique_col1_col2 UNIQUE (column1, column2);

This constraint restricts duplicate pairs in the designated columns and guarantees their combined uniqueness.

Here's a tip: when your table structure allows and both columns can't be null, consider a composite primary key:

ALTER TABLE your_table ADD PRIMARY KEY (column1, column2);

And remember, in PostgreSQL 10 and later you should use the GENERATED ALWAYS AS IDENTITY clause to add an identity column, not serial:

ALTER TABLE your_table ADD COLUMN new_id_col INTEGER GENERATED ALWAYS AS IDENTITY;

One last thing: ensure the columns in a unique constraint are always NOT NULL. Otherwise, null values can sneak around the constraint:

ALTER TABLE your_table ALTER COLUMN column1 SET NOT NULL;
ALTER TABLE your_table ALTER COLUMN column2 SET NOT NULL;

Safe schema changes with migrations

When modifying your database schema, remember to handle "up"/"down" migrations with error handling to avoid losing data:

An "Up" migration creates or modifies the schema.
A "Down" migration reverts the changes if any step of the "up" migration doesn't go as planned.

These steps provide a safety net for you to iterate schema design without risking the integrity of existing data.

Changing constraints and columns: Handle with care

If you ever need to remove a unique constraint or even an entire column, remember the corresponding ALTER TABLE commands:

ALTER TABLE your_table DROP CONSTRAINT unique_col1_col2; -- Oops, didn't mean to do that!
ALTER TABLE your_table DROP COLUMN column_name; -- Bye-bye, column. It's not you, it's us.

But handle these commands with care, their impact can snuck up on your application logic or even the integrity of your data.

Protecting data integrity

Applying a composite UNIQUE constraint ensures each pair of values in your columns is distinct, bolstering data integrity:

CREATE TABLE example (
    id INTEGER,
    col1 INTEGER NOT NULL,
    col2 INTEGER NOT NULL,
    UNIQUE (col1, col2) -- Unique or not unique, that is the question.
);

If you want to go down the rabbit hole and chase more examples, check out PostgreSQL's DDL Constraints:

PostgreSQL Documentation.

Handling constraint conflicts gracefully

When a constraint violation rears its ugly head, make sure you handle the errors gracefully. Implementing error catching mechanisms can help inform the user about this unwelcome guest–the duplicate entry.

A couple of strategies to consider:

Conflict Avoidance: Double-check the entry before insertion.
Conflict Handling: Use the ON CONFLICT clause in your INSERT commands to decide what to do when a constraint conflict happens.

INSERT INTO your_table (column1, column2) VALUES (value1, value2)
ON CONFLICT ON CONSTRAINT unique_col1_col2 DO NOTHING; 
// When SQL gives you conflicts, make conflict-ade.