PostgresSQL: Validate specific value exists once in the column

user_2666812 0
Viewed 44

I'm trying to check the uniqueness where for the same values in the column APP will exist only specific value in the column STATUS.

Example: Each app can be only once "true".
APP cannot be a PRIMARY KEY.
The DB should restrict adding additional row with (app1, true) and permit (app1, false)

ID APP STATUS
1 app1 false
2 app1 true
3 app1 false
4 app2 false
5 app2 false
6 app2 false
7 app2 true

Maybe constraint, or indexing can help here?

unique indexing constraints postgresql
1 Answers

Per @lemon suggestion in comments you could use a trigger or as an alternative:

create table unique_test (
   id integer, 
   app varchar, 
   status boolean, 
   unique(app, status));

insert into unique_test values (1, 'app1', null);
insert into unique_test values (2, 'app2', null);
insert into unique_test values (3, 'app1', 'true');
insert into unique_test values (4, 'app1',  null);

insert into unique_test values (5, 'app1',  'true');
ERROR:  duplicate key value violates unique constraint 
"unique_test_app_status_key"
DETAIL:  Key (app, status)=(app1, t) already exists.

insert into unique_test values (5, 'app1',  null);
insert into unique_test values (6, 'app2',  'true');

select * from unique_test ;
 id | app  | status 
----+------+--------
  1 | app1 | NULL
  2 | app2 | NULL
  3 | app1 | t
  4 | app1 | NULL
  5 | app1 | NULL
  6 | app2 | t

This takes advantage of the fact that UNIQUE sees NULL's as unique from each other. FYI in the upcoming Postgres 15 this behavior can be modified so that is not so, see Unique and NULLS.

user_70706130
Related