PostgresSQL: Validate specific value exists once in the column-CodePudding

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?

CodePudding user response：

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.