PostgreSQL Unique Constraint - only one row for two columns

Question

I have a PostgreeSQL table

CREATE TABLE metadado_log_audit (
    source_table varchar NULL,
    name_column varchar NULL,
    is_id_relation bool NULL
);

How can I set constraint that only one row have is_id_relation = true and unique source_table

Ex. I can not save source_table = 'user' and is_id_realation = true twice Tks

Answer 1

You seem to want a constraint that enforces unicity of source_table where is_id_relation is true.

In Postgres, you can use a unique filtered index for this:

create unique index metadado_log_audit_idx 
    on metadado_log_audit(source_table) 
    where is_id_relation;

Demo on fiddle:

insert into metadado_log_audit (source_table, is_id_relation) values ('user', true);
-- ok
 
insert into metadado_log_audit (source_table, is_id_relation) values ('user', false);
-- ok

insert into metadado_log_audit (source_table, is_id_relation) values ('user', true);
-- ERROR:  duplicate key value violates unique constraint "metadado_log_audit_idx"
-- DETAIL:  Key (source_table)=(user) already exists.

---

Note that Postgres unique indexes do allow duplicate null values. In Postgres 15, you can change that behavior with option nulls not distinct:

create unique index metadado_log_audit_idx 
    on metadado_log_audit(source_table) 
    nulls not distinct
    where is_id_relation;