Question 89155 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL disallow having more than one row satisfying the condition

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.

Tags:

3

Answers


  1. 0

    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[] );
    Login or Signup to reply.
  2. 0

    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'));
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search