SyntaxFix

[postgresql] PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

Postgres hasn't implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICT docs (emphasis mine):

It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict.

Though it doesn't give you shorthand for replacement, ON CONFLICT DO UPDATE applies more generally, since it lets you set new values based on preexisting data. For example:

INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;

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 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 postgresql-9.5

• PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values • Postgresql - unable to drop database because of some auto connections to DB