I have two colums, foo and bar. foo can only exist with one bar, but can exists n times. If I try to insert foo with a different value of bar, it needs to fail.
foo | bar
----------
a | a
a | a
b | a
b | a
b | b <--- this needs to fail.
How can I solve this?
And somewhat tangentially what is the proper language for this question?
You can use an EXCLUDE constraint.
Using the extension btree_gist:
CREATE EXTENSION btree_gist;
You can define a table
CREATE TABLE fb (
foo text,
bar text,
EXCLUDE USING gist (foo WITH =, bar WITH <>)
);
such that the following insertion is ok:
#= INSERT INTO fb VALUES ('a', 'a'), ('a', 'a'), ('b', 'b');
INSERT 0 3
Time: 1,392 ms
#= SELECT * FROM fb;
┌─────┬─────┐
│ foo │ bar │
├─────┼─────┤
│ a │ a │
│ a │ a │
│ b │ b │
└─────┴─────┘
(3 rows)
but inserting the ('b', 'a') tuple fails because 'b' already has a bar = 'b' row.
#= INSERT INTO fb VALUES ('b', 'a');
ERROR: 23P01: conflicting key value violates exclusion constraint "fb_foo_bar_excl"
DETAIL: Key (foo, bar)=(b, a) conflicts with existing key (foo, bar)=(b, b).
SCHEMA NAME: public
TABLE NAME: fb
CONSTRAINT NAME: fb_foo_bar_excl
Edit: Though this works I agree with Conrad Frix's comment, normalizing your table is probably a way better idea.
