CODEWITHSUNDEEP
postgresqltriggersconstraintsexclusion-constraint
dekpoke
dekpoke

Reputation: 3

Postgres exclusion constraint on insert/update

I have a table defined like so

                               Table "public.foo"
  Column  |  Type   | Collation | Nullable |               Default               
----------+---------+-----------+----------+-------------------------------------
 foo_id   | integer |           | not null | nextval('foo_foo_id_seq'::regclass)
 bar_id   | integer |           |          | 
 approved | boolean |           |          | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (foo_id)
Foreign-key constraints:
    "foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES bar(bar_id)

How would I define an exclusion constraint, such that only one row of foo with a specific bar_id would be able to set approved to true?

For example with the following data:

 foo_id | bar_id | approved 
--------+--------+----------
      1 |      1 | t
      2 |      1 | 
      3 |      2 | 
(3 rows)

I would be able to set approved to row 3 to true, because no other row with foo_id 3 has true for approved.

However updating row 2 's approved to true would fail, because row 1 also has foo_id 1 and is already approved.

Upvotes: 0

Views: 342

Answers (2)

Alan Millirud
Alan Millirud

Reputation: 1172

try this

ALTER TABLE public.foo
ADD CONSTRAINT uniq_approved UNIQUE (bar_id, approved)

or you can create unique index

CREATE UNIQUE INDEX uniq_approved ON public.foo
USING btree (bar_id, approved)
WHERE approved

Upvotes: 1

user330315
user330315

Reputation:

You don't need an exclusion constraint, a filtered unique index will do:

create unique index only_one_approved_bar 
   on foo (bar_id)
   where approved;

I would also recommend to define approved as not null. Boolean columns that allow null values are typically a source of constant confusion.

Upvotes: 3

Related Questions