Preventing duplication on a Postgres table with 2 columns I am currently implementing a simple database schema (in PostgreSQL 11.1 (Debian)), with two int columns (say col1, col2).

My goal is to prevent duplicates appearing in any of the two columns.  If (1,2) already existed, then (1,2) or (2,1) would cause a "duplicate key value violates unique constraint" error.

For a practical example.  I have created a table someTable, with col1, col2, with a unique constraint (col1, col2).  Everything is fine when I insert (1,2) a second time:

CREATE TABLE someTable (
 col1 int NOT NULL,
 col2 int NOT NULL,
 unique (col1, col2));

- CREATE TABLE

INSERT INTO someTable (col1, col2) VALUES (1,2);
- INSERT 0 1

INSERT INTO someTable (col1, col2) VALUES (1,2);
- ERROR:  duplicate key value violates unique constraint "sometable_col1_col2_key"
DETAIL:  Key (col1, col2)=(1, 2) already exists.

INSERT INTO someTable (col1, col2) VALUES (2,1);
- INSERT 0 1


I would like the (col1, col2) unique constraint to work for swapped values (for example 2,1 is treated the same as 1,2).
Enforce a particular canonical ordering on the values:

alter table sometable add constraint sdljfsdfj check (col1&lt;=col2);


If they are in the wrong order, the check constraint will reject them.  They are in the correct order the unique constraint will.

You could couple this with a BEFORE INSERT trigger to swap the order, if you don't want to force the application to deal with that logic.