I want to only insert a row if every cell in that row is unique (excluding the id field of course) so I tried this
INSERT INTO test (val, val2, val3) VALUES ('g', 'h', 'j')
ON CONFLICT (val, val2, val3) DO NOTHING RETURNING id;
But turns out that this is invalid because the fields passed to ON CONFLICT
must be constrained with UNIQUE
. But I don't want that, I only want permutations of val, val2 and var3 to be unique, I don't want them to be unique individually. And after the INSERT, no matter if it inserted or not I want to return the id field. How may I do this?
I solved this by creating the UNIQUE like this:
CREATE TABLE test (
id SERIAL,
val varchar(100),
val2 varchar(100),
val3 varchar(100),
UNIQUE (val, val2, val3)
);
and then inserting like this:
INSERT INTO test (val, val2, val3) VALUES ('a', 'b', 'c') ON CONFLICT (val, val2, val3) DO UPDATE SET val='a' RETURNING id;
even if there is nothing to update, it makes it return the id