UPDATE from temp table picking the "last" row per group

Question

Suppose there is a table with data:

+----+-------+
| id | value |
+----+-------+
|  1 |     0 |
|  2 |     0 |
+----+-------+

I need to do a bulk update. And use COPY FROM STDIN for fast insert to temp table without constraints and so it can contains duplicate values in id column

Temp table to update from:

+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  1 |     2 |
|  2 |     2 |
+----+-------+

If I simply run a query like with:

UPDATE test target SET value = source.value FROM tmp_test source WHERE target.id = source.id;

I got wrong results:

+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     1 |
+----+-------+

I need the target table to contain the values that appeared last in the temporary table.

What is the most effective way to do this, given that the target table may contain millions of records, and the temporary table may contain tens of thousands?**

Please define "last". There is no order in a relational table unless defined by `ORDER BY`. You mean inserted last, physically? — Erwin Brandstetter, Jul 02 '20 at 21:34
Yes, last in the temporary table is the last value written there. In my example this is '2' — Alew, Jul 02 '20 at 21:39

Erwin Brandstetter · Accepted Answer · 2020-07-02T21:47:47.170

Assuming you want to take the value from the row that was inserted last into the temp table, physically, you can (ab-)use the system column ctid, signifying the physical location:

UPDATE test AS target
SET    value = source.value
FROM  (
   SELECT DISTINCT ON (id)
          id, value
   FROM   tmp_test
   ORDER  BY id, ctid DESC
   ) source
WHERE  target.id = source.id
AND    target.value <> source.value;  -- skip empty updates

About DISTINCT ON:

Select first row in each GROUP BY group?

This builds on a implementation detail, and is not backed up by the SQL standard. If some insert method should not write rows in sequence (like future "parallel" INSERT), it breaks. Currently, it should work. About ctid:

How do I decompose ctid into page and row numbers?

If you want a safe way, you need to add some user column to signify the order of rows, like a serial column. But do your really care? Your tiebreaker seems rather arbitrary. See:

Temporary sequence within a SELECT

AND target.value <> source.value

skips empty updates - assuming both columns are NOT NULL. Else, use:

AND target.value IS DISTINCT FROM source.value

See:

How do I (or can I) SELECT DISTINCT on multiple columns?

UPDATE from temp table picking the "last" row per group

1 Answers1