]> git.ipfire.org Git - thirdparty/postgresql.git/commit
Add support for INSERT ... ON CONFLICT DO SELECT.
authorDean Rasheed <dean.a.rasheed@gmail.com>
Thu, 12 Feb 2026 09:55:06 +0000 (09:55 +0000)
committerDean Rasheed <dean.a.rasheed@gmail.com>
Thu, 12 Feb 2026 09:57:04 +0000 (09:57 +0000)
commit88327092ff06c48676d2a603420089bf493770f3
treed21b77cc5efab88f2e0f5b2c47a4ea41d8450497
parent788ec96d591d0a7c916f2f4332765f46410d73b5
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
48 files changed:
contrib/postgres_fdw/postgres_fdw.c
doc/src/sgml/dml.sgml
doc/src/sgml/fdwhandler.sgml
doc/src/sgml/mvcc.sgml
doc/src/sgml/postgres-fdw.sgml
doc/src/sgml/ref/create_policy.sgml
doc/src/sgml/ref/create_view.sgml
doc/src/sgml/ref/insert.sgml
doc/src/sgml/ref/merge.sgml
src/backend/access/heap/heapam.c
src/backend/commands/explain.c
src/backend/executor/execIndexing.c
src/backend/executor/execPartition.c
src/backend/executor/nodeModifyTable.c
src/backend/optimizer/plan/createplan.c
src/backend/optimizer/plan/setrefs.c
src/backend/optimizer/util/plancat.c
src/backend/parser/analyze.c
src/backend/parser/gram.y
src/backend/parser/parse_clause.c
src/backend/rewrite/rewriteHandler.c
src/backend/rewrite/rowsecurity.c
src/backend/utils/adt/ruleutils.c
src/include/catalog/catversion.h
src/include/nodes/execnodes.h
src/include/nodes/lockoptions.h
src/include/nodes/nodes.h
src/include/nodes/parsenodes.h
src/include/nodes/plannodes.h
src/include/nodes/primnodes.h
src/test/isolation/expected/insert-conflict-do-select.out [new file with mode: 0644]
src/test/isolation/isolation_schedule
src/test/isolation/specs/insert-conflict-do-select.spec [new file with mode: 0644]
src/test/regress/expected/constraints.out
src/test/regress/expected/insert_conflict.out
src/test/regress/expected/privileges.out
src/test/regress/expected/rowsecurity.out
src/test/regress/expected/rules.out
src/test/regress/expected/triggers.out
src/test/regress/expected/updatable_views.out
src/test/regress/sql/constraints.sql
src/test/regress/sql/insert_conflict.sql
src/test/regress/sql/privileges.sql
src/test/regress/sql/rowsecurity.sql
src/test/regress/sql/rules.sql
src/test/regress/sql/triggers.sql
src/test/regress/sql/updatable_views.sql
src/tools/pgindent/typedefs.list