Add support for INSERT ... ON CONFLICT DO SELECT.

Dean Rasheed <dean.a.rasheed@gmail.com>

Commit: 88327092ff06c48676d2a603420089bf493770f3
Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2026-02-12T09:57:04Z
Add support for INSERT ... ON CONFLICT DO SELECT.

This adds a new ON CONFLICT action DO SELECT [FOR UPDATE/SHARE], which
returns the pre-existing rows when conflicts are detected. The INSERT
statement must have a RETURNING clause, when DO SELECT is specified.

The optional FOR UPDATE/SHARE clause allows the rows to be locked
before they are are returned. As with a DO UPDATE conflict action, an
optional WHERE clause may be used to prevent rows from being selected
for return (but as with a DO UPDATE action, rows filtered out by the
WHERE clause are still locked).

Bumps catversion as stored rules change.

Author: Andreas Karlsson <andreas@proxel.se>
Author: Marko Tiikkaja <marko@joh.to>
Author: Viktor Holmberg <v@viktorh.net>
Reviewed-by: Joel Jacobson <joel@compiler.org>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
Discussion: https://postgr.es/m/5fca222d-62ae-4a2f-9fcb-0eca56277094@Spark
Discussion: https://postgr.es/m/2b5db2e6-8ece-44d0-9890-f256fdca9f7e@proxel.se
Discussion: https://postgr.es/m/CAL9smLCdV-v3KgOJX3mU19FYK82N7yzqJj2HAwWX70E=P98kgQ@mail.gmail.com

Files

PathChange+/−
contrib/postgres_fdw/postgres_fdw.c modified +1 −1
doc/src/sgml/dml.sgml modified +1 −1
doc/src/sgml/fdwhandler.sgml modified +1 −1
doc/src/sgml/mvcc.sgml modified +12 −0
doc/src/sgml/postgres-fdw.sgml modified +1 −1
doc/src/sgml/ref/create_policy.sgml modified +23 −6
doc/src/sgml/ref/create_view.sgml modified +2 −2
doc/src/sgml/ref/insert.sgml modified +102 −33
doc/src/sgml/ref/merge.sgml modified +2 −1
src/backend/access/heap/heapam.c modified +4 −4
src/backend/commands/explain.c modified +31 −5
src/backend/executor/execIndexing.c modified +3 −3
src/backend/executor/execPartition.c modified +78 −56
src/backend/executor/nodeModifyTable.c modified +289 −86
src/backend/optimizer/plan/createplan.c modified +4 −0
src/backend/optimizer/plan/setrefs.c modified +3 −2
src/backend/optimizer/util/plancat.c modified +18 −9
src/backend/parser/analyze.c modified +36 −18
src/backend/parser/gram.y modified +19 −1
src/backend/parser/parse_clause.c modified +8 −6
src/backend/rewrite/rewriteHandler.c modified +20 −7
src/backend/rewrite/rowsecurity.c modified +63 −48
src/backend/utils/adt/ruleutils.c modified +48 −29
src/include/catalog/catversion.h modified +1 −1
src/include/nodes/execnodes.h modified +7 −6
src/include/nodes/lockoptions.h modified +2 −1
src/include/nodes/nodes.h modified +1 −0
src/include/nodes/parsenodes.h modified +6 −4
src/include/nodes/plannodes.h modified +3 −1
src/include/nodes/primnodes.h modified +9 −3
src/test/isolation/expected/insert-conflict-do-select.out added +138 −0
src/test/isolation/isolation_schedule modified +1 −0
src/test/isolation/specs/insert-conflict-do-select.spec added +53 −0
src/test/regress/expected/constraints.out modified +4 −0
src/test/regress/expected/insert_conflict.out modified +212 −4
src/test/regress/expected/privileges.out modified +26 −0
src/test/regress/expected/rowsecurity.out modified +93 −1
src/test/regress/expected/rules.out modified +55 −0
src/test/regress/expected/triggers.out modified +9 −1
src/test/regress/expected/updatable_views.out modified +80 −2
src/test/regress/sql/constraints.sql modified +3 −0
src/test/regress/sql/insert_conflict.sql modified +71 −3
src/test/regress/sql/privileges.sql modified +18 −0
src/test/regress/sql/rowsecurity.sql modified +56 −1
src/test/regress/sql/rules.sql modified +26 −0
src/test/regress/sql/triggers.sql modified +2 −1
src/test/regress/sql/updatable_views.sql modified +29 −2
src/tools/pgindent/typedefs.list modified +1 −1

Discussion