Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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'))
);

CREATE TYPE



CREATE TABLE


insert into orders (user_id,status) values
(1,'pending'),
(2,'in_process'),
(3,'pending'),
(3,'completed'),
(3,'declined'),
(4,'in_process'),
(4,'declined'),--multiple `declined` for the same user_id are allowed
(4,'declined'),
(4,'completed'),--multiple `completed` for the same user_id are allowed
(4,'completed')
returning *;


id
user_id
status


1
1
pending

2
2
in_process

3
3
pending

4
3
completed

5
3
declined

6
4
in_process

7
4
declined

8
4
declined

9
4
completed

10
4
completed



INSERT 0 10


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
(2,'pending');

ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
DETAIL:  Key (user_id)=(2) conflicts with existing key (user_id)=(2).


insert into orders (user_id,status) values
(2,'in_process');

ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
DETAIL:  Key (user_id)=(2) conflicts with existing key (user_id)=(2).


--multiple `completed` and `declined` for the same user_id are allowed
insert into orders (user_id,status) values
(1,'completed'),
(1,'completed'),
(1,'completed'),
(1,'declined'),
(1,'declined'),
(1,'declined');

INSERT 0 6


--multiple `completed` and `declined` for the same user_id are allowed
insert into orders (user_id,status) values
(2,'completed'),
(2,'completed'),
(2,'completed'),
(2,'declined'),
(2,'declined'),
(2,'declined');

INSERT 0 6


--update for user_id=1 re-enables insertion of new `pending` or `in_process`
update orders set status='declined' where id=1;
insert into orders (user_id,status) values
(1,'pending');

UPDATE 1



INSERT 0 1
id	user_id	status
1	1	pending
2	2	in_process
3	3	pending
4	3	completed
5	3	declined
6	4	in_process
7	4	declined
8	4	declined
9	4	completed
10	4	completed