In Postgresql, Force Unique on Combination of Two Columns

In Postgresql, force unique on combination of two columns

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)
)

Unique constraint for 2 columns that works both ways

You can create a unique index that always indexes the same order of values:

create unique index 
   on friends (least(requestor, requestee), greatest(requestor, requestee));

Postgresql enforce unique two-way combination of columns

A variation on Neil's solution which doesn't need an extension is:

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

Neil's solution lets you use an arbitrary number of columns though.

We're both relying on using expressions to build the index which is documented
https://www.postgresql.org/docs/current/indexes-expressional.html

Unique constrain for values combination in Postgresql

You can't do this with a consraint. However, in Postgres, a partial unique index would do exactly what you want:

create unique index myindex on mytable(code) where (deleted = false);

You can also phrase this as:

create unique index myindex on mytable(code) where (not deleted);

Unique constraint on combination of two columns?

You can do this using an index on expressions:

create unique index unq_test_a_b on (test(least(a, b), greatest(a, b));

I don't think the unique constraint allows expressions (and don't have a convenient Postgres to test on right now), but this is essentially the same thing.

---

Related Topics