TODO¶

a] Documentation

b] How to apply | design | use in the workflow

c] Integrate tools with connectors

{'S': 'ERROR', 'V': 'ERROR', 'C': '42P16', 'M': 'cannot create temporary relation in non-temporary schema', 'P': '19', 'F': 'namespace.c', 'L': '646', 'R': 'RangeVarAdjustRelationPersistence'}
INSERT…ON CONFLICT (Upsert)

Starting with version 9.5, PostgreSQL allows “upserts” (update or insert) of rows into a table via the ON CONFLICT clause of the INSERT statement. A candidate row will only be inserted if that row does not violate any unique constraints. In the case of a unique constraint violation, a secondary action can occur which can be either “DO UPDATE”, indicating that the data in the target row should be updated, or “DO NOTHING”, which indicates to silently skip this row.

Conflicts are determined using existing unique constraints and indexes. These constraints may be identified either using their name as stated in DDL, or they may be inferred by stating the columns and conditions that comprise the indexes.

SQLAlchemy provides ON CONFLICT support via the PostgreSQL-specific insert() function, which provides the generative methods Insert.on_conflict_do_update() and Insert.on_conflict_do_nothing():

from sqlalchemy.dialects.postgresql import insert
insert_stmt = insert(my_table).values(
  id='some_existing_id',
  data='inserted value')
do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
    index_elements=['id']
)
print(do_nothing_stmt)
# INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
# ON CONFLICT (id) DO NOTHING
do_update_stmt = insert_stmt.on_conflict_do_update(
  constraint='pk_my_table',
  set_=dict(data='updated value')
)
print(do_update_stmt)
# INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
# ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s