PostgreSQL disallow having more than one row satisfying the condition

Question

I have a table orders with the columns: id, user_id and status. The status column is enum with values: pending, in_process, completed, declined.

I need to disallow a user to have more than one order with status in (pending, in_process).

For example, having the table:

id	status	user_id
1	completed	1
2	in_process	1
3	pending	2

We shouldn't be able to insert neither one more order with status pending nor with status in_process for the user_id=1.

I feel like I should use an exclusion constraint here, but don't know how to disallow intersection of two sets.

Answer 1

You're not looking for an exclusion constraint here (as you want to allow any number of completed rows which all have the same values for user_id and status) but rather for a conditionally unique constraint - which postgres does not support as a CONSTRAINT, but you can use a partial UNIQUE index:

CREATE UNIQUE INDEX unique_active_order
ON orders (user_id)
WHERE (status IN ('pending', 'in_process'));

or

CREATE UNIQUE INDEX unique_active_order
ON orders (user_id)
WHERE (status NOT IN ('completed','declined'));

Answer 2

What you are looking for is a partial unique index. Since status is an enum you will, most likely, have to type cast the array.

create unique index on orders (user_id, status)
where status = any ( array['pending', 'in_process']::status_enum[] );

Answer 3

Belayer and Bergi beat me to posting the partial unique approach, so for sports: you can make an exclusion constraint work for this. demo

create type status_enum as enum('pending','in_process','completed','declined');

create table orders (
   id smallserial primary key,
   user_id int,
   status status_enum default 'pending',
   exclude (user_id with =) where (status in ('pending','in_process') ) 
);

insert into orders (status,user_id) values
('completed', 1),
('in_process',1),
('pending',   2);
--INSERT 0 3
insert into orders (user_id,status) values (1,'pending');
--ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
--DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).
insert into orders (user_id,status) values (1,'in_process');
--ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
--DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).
insert into orders (user_id,status) values (1,'declined');--no problem
--INSERT 0 1

Among those where status in ('pending','in_process'), no two user_id's can be the same.

I said this is for sports because it can work, but offers no benefits over the partial unique:

Although it's allowed, there is little point in using B-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn't do better.