This article discusses INSERT ... ON CONFLICT with a focus on the DO SELECT clause new in PostgreSQL 19 and use cases for the new feature

CYBERTEC PostgreSQL

PostgreSQL v19 introduces a new `ON CONFLICT ... DO SELECT` clause for `INSERT` statements. This extends the existing upsert functionality by allowing you to retrieve rows that already existed in the table when a conflict occurs, rather than only returning newly inserted rows. The feature is most useful when combined with `INSERT ... RETURNING` and when tables have generated columns or trigger-modified data. Previously, achieving the same result required either a separate `SELECT` statement or a no-op `DO UPDATE SET col = col` workaround, which had the downside of creating dead rows that VACUUM must later clean up. The post also explains why `INSERT ... ON CONFLICT` is preferred over `MERGE` for concurrent workloads, as `MERGE` can fail with a duplicate key error under concurrent inserts.

INSERT ... ON CONFLICT ... DO SELECT: a new feature in PostgreSQL v19

Description of the new ON CONFLICT ... DO SELECT

Working around the lack of ON CONFLICT ... DO SELECT in older PostgreSQL versions

Why is there still no ON CONFLICT ... DO DELETE ?