Unique Value Constraint Across Multiple Columns

Suppose, I have the following table: CREATE TABLE 'user' ( id BIGINT PRIMARY KEY NOT NULL, phone1 VARCHAR, phone2 VARCHAR ); And I need to implement the following

Solution 1:

You cannot easily do this. The least()/greatest() approach will not work in all cases.

Postgres does have some fancy index operations. But the best way is to use a junction table. For instance:

createtable userPhones (
    userPhoneId bigintprimary key ,
    userId bigintreferences users(id),
    phone_counter intcheck (phone_counter in (1, 2)),
    phone varchar,
    unique (userId, phone_counter),
    unique(phone)
);

This also limits the number of phone numbers to 2 for each user.

Solution 2:

try an old trick:

db=# createunique index on "user" (least(phone1,phone2), greatest(phone1,phone2));
CREATE INDEX
Time: 14.507 ms
db=# insertinto "user" values(1,111,111);
INSERT01Time: 35.017 ms

rest will fail:

db=# insertinto "user" values(2,111,null);
ERROR:  duplicate key value violates uniqueconstraint "user_least_greatest_idx"
DETAIL:  Key ((LEAST(phone1, phone2)), (GREATEST(phone1, phone2)))=(111, 111) already exists.
Time: 10.323 ms
db=# insertinto "user" values(2,null,111);
ERROR:  duplicate key value violates uniqueconstraint "user_least_greatest_idx"
DETAIL:  Key ((LEAST(phone1, phone2)), (GREATEST(phone1, phone2)))=(111, 111) already exists.
Time: 5.553 ms
db=# insertinto "user" values(1,111,111);
ERROR:  duplicate key value violates uniqueconstraint "user_pkey"
DETAIL:  Key (id)=(1) already exists.
Time: 11.067 ms