Hi all
We recently moved to using 9.5 and were hoping to use the new upsert functionality, but unfortunately it doesn’t quite
dowhat we need.
Our setup is something like this:
CREATE TABLE t1 ( id BIGSERIAL NOT NULL PRIMARY KEY, bk1 INT, bk2 UUID — other columns
);
CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2);
CREATE TABLE t2 ( t1_id BIGINT NOT NULL REFERENCES t1— other stuff
);
Data comes in as inserts of one tuple each of t1 and t2. We expect inserts to t1 to be heavily duplicated. That is, for
stuffcoming in we expect a large number of rows to have duplicate (bk1, bk2), and we wish to discard those, but not
discardthe t2 tuple - those should always be inserted and reference the correct t1 record.
So we currently have an insertion function that does this:
BEGIN INSERT INTO t1 (bk1, bk2, other columns) VALUES (bk1val, bk2val, other values) RETURNING id INTO t1_id;
EXCEPTION WHEN unique_violation THEN SELECT id FROM t1 WHERE bk1 = bk1val AND bk2 = bk2val INTO t1_id;
END;
INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);
We were hoping that we’d be able to do something like this:
INSERT INTO t1 (bk1, bk2, other columns) VALUES (bk1val, bk2val, other values) ON CONFLICT (bk1val, bk2val) DO NOTHING
RETURNINGid INTO t1_id;
INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);
But unfortunately it seems that the RETURNING clause returns null when there’s a conflict, rather than the existing
row’svalue.
I understand that there is ambiguity if there were multiple rows that were in conflict. I think this sort of
functionalityreally only makes sense where the conflict target is a unique constraint, so IMO it would make sense to
onlysupport returning columns in that case.
I imagine that this would be possible to do more efficiently than the subsequent query that we are currently doing
giventhat postgres has already found the rows in question, in the index at least. I have no idea how hard it would
actuallybe to implement though. FWIW my use-case would be supported even if this only worked for indexes where the
to-be-returnedcolumns were stored in the index using Anastasia’s covering + unique index patch, when that lands.
Thoughts?
Tom
