Postgres 13.4.
I got a request today to build what amounts to a conditional ON CONFLICT statement. The idea is that we get readings from various instruments that provide a timestamp. This is an approximation of our situation, we might be using a timestamp or version number. Either way, the value is built outside of Postgres. We trust that external timestamp or version number. However, readings can come in out-of-order, and we always want to keep the latest one. So, regardless of INSERT sequence, the latest copy is kept. I have a solution working in a toy test, and I'm wondering if I'm missing a better strategy, or what I've got listed below has holes I don't expect.
For the toy test, a simple table:
DROP TABLE IF EXISTS reading;
CREATE TABLE reading (
instrument_number int4 NOT NULL DEFAULT NULL PRIMARY KEY,
reading_dts timestamp NOT NULL DEFAULT NULL,
note text NOT NULL DEFAULt ''
);
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (1, '2022-01-15 15:30:45', 'Red');
select note from reading where instrument_number = 1 -- Red
What we're usually doing is shown below. Namely, use an ON CONFLICT on a PK or UNIQUE constraint to favor the incoming record, regardless of its contents.
-- Duplicate key error, as expected:
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (1, '2022-01-15 15:30:45','Blue');
select note from reading where instrument_number = 1 -- Red
-- Resolves PK conflict:
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (1, '2022-01-15 15:30:45','Blue')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note;
select note from reading where instrument_number = 1 -- Blue
The new wrinkle is that we want to keep the copy of the row with the latest reading_dts value. But there's no if logic in SQL like that. I noticed in the docs that ON CONFLICT supports a WHERE clause, and it seems to work:
-- INSERT Earlier then Later: Later wins.
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (2, '2022-01-15 15:30:45','Earlier')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note
WHERE EXCLUDED.reading_dts > reading.reading_dts;
select note from reading where instrument_number = 2 -- Later
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (2, '2022-01-15 15:30:45','Later')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note
WHERE EXCLUDED.reading_dts > reading.reading_dts;
select note from reading where instrument_number = 2 -- Later
DELETE from reading WHERE instrument_number = 2;
-- INSERT Later then Earlier: Later wins.
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (2, '2022-01-15 15:30:45','Later')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note
WHERE EXCLUDED.reading_dts > reading.reading_dts;
select note from reading where instrument_number = 2 -- Later
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (2, '2022-01-15 15:30:45','Earlier')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note
WHERE EXCLUDED.reading_dts > reading.reading_dts;
select note from reading where instrument_number = 2 -- Later
One thing that makes me a bit wary here is that I had to add reading. to qualify the reference to the existing record. Here's what happens otherwise:
INSERT INTO reading (instrument_number, reading_dts, note)
VALUES (2, '2022-01-15 15:30:45','Later')
ON CONFLICT ON CONSTRAINT reading_pkey
DO UPDATE SET reading_dts = EXCLUDED.reading_dts,
note = EXCLUDED.note
WHERE EXCLUDED.reading_dts > reading_dts;
ERROR: column reference "reading_dts" is ambiguous
LINE 6: WHERE EXCLUDED.reading_dts > reading_dts;
I didn't find a name for THIS., or whatever, in the docs and tried the table's name. It seemed to work. But, I don't want to build a plan on top of that.
Any advice or additional information appreciated.