From 4e9ab7a72f0ad506cf519069fd67127f63e5f2aa Mon Sep 17 00:00:00 2001 From: Robin Thomas Date: Thu, 14 Apr 2016 12:57:15 -0400 Subject: Add ON CONFLICT support for Postgresql Fixes: #3529 Co-authored-by: Mike Bayer Change-Id: Ie3bf6ad70d9be9f0e44938830e922db03573991a Pull-request: https://github.com/zzzeek/sqlalchemy/pull/258 --- doc/build/changelog/changelog_11.rst | 12 ++++++++++++ doc/build/changelog/migration_11.rst | 36 ++++++++++++++++++++++++++++++++++++ doc/build/dialects/postgresql.rst | 8 ++++++++ 3 files changed, 56 insertions(+) (limited to 'doc') diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 8d20ef257..6151ec3ff 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -21,6 +21,18 @@ .. changelog:: :version: 1.1.0b1 + .. change:: + :tags: feature, postgresql + :tickets: 3529 + + Added support for Postgresql's INSERT..ON CONFLICT using a new + Postgresql-specific :class:`.postgresql.dml.Insert` object. + Pull request and extensive efforts here by Robin Thomas. + + .. seealso:: + + :ref:`change_3529` + .. change:: :tags: feature, postgresql :pullreq: bitbucket:84 diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index 73483f3db..ea932b509 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -2026,6 +2026,42 @@ necessary to worry about the names themselves in the textual SQL. Dialect Improvements and Changes - Postgresql ============================================= +.. _change_3529: + +Support for INSERT..ON CONFLICT (DO UPDATE | DO NOTHING) +-------------------------------------------------------- + +The ``ON CONFLICT`` clause of ``INSERT`` added to Postgresql as of +version 9.5 is now supported using a Postgresql-specific version of the +:class:`.Insert` object, via :func:`sqlalchemy.dialects.postgresql.dml.insert`. +This :class:`.Insert` subclass adds two new methods :meth:`.Insert.on_conflict_do_update` +and :meth:`.Insert.on_conflict_do_nothing` which implement the full syntax +supported by Posgresql 9.5 in this area:: + + from sqlalchemy.dialects.postgresql import insert + + insert_stmt = insert(my_table). \\ + values(id='some_id', data='some data to insert') + + do_update_stmt = insert_stmt.on_conflict_do_update( + index_elements=[my_table.c.id], + set_=dict(data='some data to update') + ) + + conn.execute(do_update_stmt) + +The above will render:: + + INSERT INTO my_table (id, data) + VALUES (:id, :data) + ON CONFLICT id DO UPDATE SET data=:data_2 + +.. seealso:: + + :ref:`postgresql_insert_on_conflict` + +:ticket:`3529` + .. _change_3499_postgresql: ARRAY and JSON types now correctly specify "unhashable" diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst index b4c90643d..56b14a8d0 100644 --- a/doc/build/dialects/postgresql.rst +++ b/doc/build/dialects/postgresql.rst @@ -181,6 +181,14 @@ For example:: ExcludeConstraint(('room', '='), ('during', '&&')), ) +PostgreSQL DML Constructs +--------------------------- + +.. autofunction:: sqlalchemy.dialects.postgresql.dml.insert + +.. autoclass:: sqlalchemy.dialects.postgresql.dml.Insert + :members: + psycopg2 -------- -- cgit v1.2.1