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.
try this
or you can create unique index