[postgresql] How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

SQLAlchemy upsert for Postgres >=9.5

Since the large post above covers many different SQL approaches for Postgres versions (not only non-9.5 as in the question), I would like to add how to do it in SQLAlchemy if you are using Postgres 9.5. Instead of implementing your own upsert, you can also use SQLAlchemy's functions (which were added in SQLAlchemy 1.1). Personally, I would recommend using these, if possible. Not only because of convenience, but also because it lets PostgreSQL handle any race conditions that might occur.

Cross-posting from another answer I gave yesterday (https://stackoverflow.com/a/44395983/2156909)

SQLAlchemy supports ON CONFLICT now with two methods on_conflict_do_update() and on_conflict_do_nothing():

Copying from the documentation:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='[email protected]', data='inserted data')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to insert-update

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? Update a column in MySQL SQL Server insert if not exists best practice Insert into a MySQL table or update if exists INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

Examples related to upsert

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? SQLite UPSERT / UPDATE OR INSERT Insert into a MySQL table or update if exists Postgres: INSERT if does not exist already INSERT IF NOT EXISTS ELSE UPDATE? SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE" Insert, on duplicate update in PostgreSQL? How do I UPDATE a row in a table or INSERT it if it doesn't exist? SQLite - UPSERT *not* INSERT or REPLACE

Examples related to sql-merge

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? When doing a MERGE in Oracle SQL, how can I update rows that aren't matched in the SOURCE? ORA-30926: unable to get a stable set of rows in the source tables Insert, on duplicate update in PostgreSQL?