A
A
Artem2020-07-22 09:50:43
PostgreSQL
Artem, 2020-07-22 09:50:43

How to create a unique index with a condition?

id | contractor_id | is_main (bool)
-------------------------------------------------------
1  | 1             | true 
2  | 2             | true
3  | 1             | true

How to create a unique index that will ensure that there can only be 1 contractor_id in the table at a time, where is_main = true?

Those. so that when adding line 3 an exception is thrown?

UPD. Interested in the index, if it is not possible to create an index, please let us know

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2020-07-22
@proudmore

That's literally how it's done

create unique index on tablename (contractor_id) where is_main;

D
Dmitry, 2020-07-22
@gebrak

You can make an index on two columns at once:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    contractor_id INT NOT NULL,
    is_main BOOLEAN NOT NULL,
    UNIQUE (contractor_id, is_main)
);

https://www.postgresqltutorial.com/postgresql-uniq...
or
CREATE UNIQUE INDEX CONCURRENTLY contractor_id_main ON table_name (contractor_id, is_main);

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question