Bulk upsert from CSV in PostgreSQL

bulk-upsert-from-csv.sql

-- See https://www.postgresql.org/docs/current/sql-copy.html
-- and https://www.postgresql.org/docs/current/sql-insert.html

CREATE TEMP TABLE temp_table
(LIKE table INCLUDING DEFAULTS)
ON COMMIT DROP;

COPY temp_table (col1, col2, ...) FROM STDIN WITH CSV;
-- Assumes no header row: add `HEADER` if it has one.

INSERT INTO table
SELECT * FROM temp_table
ON CONFLICT (pk) DO UPDATE
SET (col1=EXLCLUDED.col1, col2=EXCLUDED.col2, ...)
WHERE EXCLUDED.timestamp > table.timestamp;
-- `ON CONFLICT DO NOTHING` can also be sufficient for some use cases.