Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table users_types_brands(
        id              serial    primary key      
      , inserted_at     timestamp without time zone default now()                                                     
      , updated_at      timestamp without time zone default now()                                                   
      , users_types_id  bigint                                                                                               
      , brand_id        bigint            not null                                                              
      , tasks_type_id   integer
      , constraint      business_key unique(users_types_id, brand_id, tasks_type_id)
      ) ; 
-- *** CREATE THE PARTIAL UNIQUE INDEX *** 
create unique index tasks_type_id_just_1_unique
    on users_types_brands (users_types_id, brand_id)
  where tasks_type_id is null;
-- insert row with Null tasks_type_id users_types_id, brand_id
insert into users_types_brands (users_types_id, brand_id) values (2, 112);
1 rows affected

-- create a 2nd Null tasks_type_id with same users_types_id, brand_id 
insert into users_types_brands (users_types_id, brand_id) values (2, 112); 
ERROR:  duplicate key value violates unique constraint "tasks_type_id_just_1_unique"
DETAIL:  Key (users_types_id, brand_id)=(2, 112) already exists.


-- insert row with Null tasks_type_id but different users_types_id, brand_id
insert into users_types_brands (users_types_id, brand_id) values (3, 112);
1 rows affected

select * from users_types_brands;


id
inserted_at
updated_at
users_types_id
brand_id
tasks_type_id


1
2022-05-19 04:47:25.357178
2022-05-19 04:47:25.357178
2
112
null

3
2022-05-19 04:47:25.359764
2022-05-19 04:47:25.359764
3
112
null

id	inserted_at	updated_at	users_types_id	brand_id	tasks_type_id
1	2022-05-19 04:47:25.357178	2022-05-19 04:47:25.357178	2	112	null
3	2022-05-19 04:47:25.359764	2022-05-19 04:47:25.359764	3	112	null