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;
Top answer 1 of 2
312
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;
2 of 2
48
Unfortunately there isn't a shorter way to write that. You MUST specify each column you want to update in the do update section.
INSERT INTO tablename (id, username, password, level, email, update_count)
-- if id doesn't exist, do insert
VALUES (1, 'John', 'qwerty', 5, '[email protected]', 0)
-- how to check for duplicates (more versatile: could use any unique index here)
ON CONFLICT (id)
DO UPDATE
SET
-- update duplicate clause
username=EXCLUDED.username, -- references proposed insertion row
password=EXCLUDED.password,
level=EXCLUDED.level,
email=EXCLUDED.email,
update_count=tablename.update_count+1 -- reference existing row
on conflict will give you something similar to insert or replace from sqlite, but it's a more versatile function that is more focused on update rather than just a full row replace.
PostgreSQL
postgresql.org › docs › current › sql-insert.html
PostgreSQL: Documentation: 18: INSERT
February 26, 2026 - INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') ON CONFLICT (did) DO NOTHING; Insert or update new distributors as appropriate. Example assumes a unique index has been defined that constrains values appearing in the did column.
postgresql - INSERT ON CONFLICT DO UPDATE SET (an UPSERT) statement with a unique constraint is generating constraint violations when run concurrently - Database Administrators Stack Exchange
ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — “UPDATE or INSERT”. which I interpreting to mean that the INSERT or UPDATE happens atomically, they should never attempt to write in data with the same primary key. For the test_table_uc constraint to be violated, then in my example... More on dba.stackexchange.com
Handling ON CONFLICT .... DO UPDATE SET for tables with two or more UNIQUE CONSTRAINTS
Your options are Rethink the schema - if you have to do something unusual (two unique constraints on the table ARE unusual), most of the time the schema is wrong. E.g split the table into two (a,b,c) and (a,b,d), define primary key for each, create a view based on two tables If you are on pg15 - use merge If version is 14 or lower use stored procedure Unique index, based on expression (function index) More on reddit.com
10
3
July 13, 2023postgres - how to correctly use INSERT INTO with UPDATE
You want an "UPSERT", aka INSERT ... ON CONFLICT ... UPDATE statement. Google 'postgresql upsert', such as this tutorial . But you must have a unique (or primary key) constraint to conflict upon, else how to know what is to be considered a new or conflicting row? Perhaps you need a compound key across columns (make, model, year) ? More on reddit.com
16
1
June 3, 2021Use On-conflict to Upsert in PostgreSQL
It's not clear to me from your name/email example why you would use this approach instead of just an update instead of an insert, but I still learned something, so thank you! More on reddit.com
5
11
February 24, 2023Videos
03:33
How to Fix PostgreSQL Upsert Syntax Error with 'ON CONFLICT' Clause?
00:59
How To Use INSERT ... ON CONFLICT DO UPDATE In PostgreSQL - YouTube
12:54
10 - UPDATE INSERT DELETE + working with conflicts (ON CONFLICT ...
12:56
10 - UPSERT и Работа с Конфликтами (ON CONFLICT ...
11:13
Postgresql Tutorials | Insert on Conflict in Postgresql | insert ...
Prisma
prisma.io › dataguide › postgresql › inserting-and-modifying-data › insert-on-conflict
Using "INSERT ON CONFLICT" to Upsert and Modify Data in PostgreSQL
The `INSERT...ON CONFLICT` construct let's you define what PostgreSQL should do if a row you are inserting conflicts with an existing row. This enables 'upsert' operations which insert records or update them on conflict.
PostgreSQL
postgresql.org › docs › 9.5 › sql-insert.html
PostgreSQL: Documentation: 9.5: INSERT
May 30, 2021 - INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; Insert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row (a row with a matching constrained column or columns after before row insert triggers fire) exists. Example assumes a unique index has been defined that constrains values appearing in the did column:
Alibaba Cloud
alibabacloud.com › help › en › analyticdb › analyticdb-for-postgresql › developer-reference › use-insert-on-conflict-to-overwrite-data
Upsert Data with INSERT ON CONFLICT in AnalyticDB PostgreSQL - AnalyticDB for PostgreSQL - Alibaba Cloud
3 weeks ago - INSERT INTO t1 VALUES (0,1,1,1) ON CONFLICT DO NOTHING; ... Use DO UPDATE SET to overwrite the existing row.
jOOQ
jooq.org › doc › latest › manual › sql-building › sql-statements › insert-statement › insert-on-conflict
The ON CONFLICT clause of the INSERT statement
insertInto(AUTHOR, AUTHOR.ID, ... DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = 'X' INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT (ID) DO UPDATE SET LAST_NAME = 'X'...
SQLite
sqlite.org › lang_conflict.html
The ON CONFLICT Clause
November 22, 2025 - The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that the syntax reads more naturally. For example, instead of "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
Beekeeper Studio
beekeeperstudio.io › blog › postgres-on-conflict
PostgreSQL ON CONFLICT Walkthrough | Beekeeper Studio
January 7, 2023 - INSERT INTO products (name, price) VALUES ('Product A', 19.99) ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price; In the second example, we are using the EXCLUDED keyword to reference the values that were attempted to be inserted.
Neon
neon.com › postgresql › postgresql-tutorial › postgresql-upsert
PostgreSQL UPSERT Statement
ON CONFLICT statement to insert a new row into the inventory table: INSERT INTO inventory (id, name, price, quantity) VALUES (1, 'A', 16.99, 120) ON CONFLICT(id) DO UPDATE SET price = EXCLUDED.price, quantity = EXCLUDED.quantity;
jOOQ
jooq.org › doc › latest › manual › sql-building › sql-statements › insert-statement › insert-on-conflict-excluded
The EXCLUDED clause of ON CONFLICT
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onConflict(AUTHOR.ID).doUpdate().set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME)) Translates to the following dialect specific expressions: INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ...
Tutorial Teacher
tutorialsteacher.com › postgresql › upsert
UPSERT Operation in PostgreSQL
Use the ON CONFLICT WHERE clause to skip or do update operation based on some condition. For example, the following executes the update statement UPDATE SET salary = 0 if a conflict occurs and also if salary > 0 in the existing row.
egghead.io
egghead.io › lessons › postgresql-on-conflict-do-something-clause-in-postgres
On conflict Do Something Clause in Postgres | egghead.io
[2:30] We reference them by this excluded terminology. By saying first_name = excluded.first_name, we're saying update the row that has the conflict with the excluded value or the value that's coming in on the INSERT statement.
Published March 6, 2020
w3resource
w3resource.com › PostgreSQL › snippets › postgresql-upsert.php
PostgreSQL Upsert: Insert or Update with ON CONFLICT
December 28, 2024 - INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON CONFLICT (conflict_column) DO UPDATE SET column1 = value1, column2 = value2;
Geshan
geshan.com.np › blog › 2024 › 12 › postgres-insert-on-conflict-update
How to Upsert Data in Postgres Using INSERT ON CONFLICT UPDATE
December 14, 2024 - The syntax is as follows: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...), ... ON CONFLICT (unique_constraint) DO UPDATE SET column1 = excluded.column1, column2 = excluded.column2, …
DB Vis
dbvis.com › thetable › postgresql-upsert-insert-on-conflict-guide
PostgreSQL Upsert: INSERT ON CONFLICT Guide
May 12, 2025 - For example, assume that the orders table has the following primary key: ... 1 INSERT INTO orders(order_id, customer_id, order_total) 2 VALUES (14, 48, 150.00) 3 ON CONFLICT (order_id, customer_id) DO UPDATE 4 SET order_total = excluded.order_total;
pganalyze
pganalyze.com › docs › log-insights › app-errors › U126
U126: ON CONFLICT DO UPDATE affected row twice · pganalyze
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time STATEMENT: INSERT INTO x (y, z) VALUES ('a', 1), ('a', 2) ON CONFLICT (y) DO UPDATE SET z = EXCLUDED.z HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
PostgreSQL Tutorial
postgresqltutorial.com › home › postgresql tutorial › postgresql upsert using insert on conflict statement
PostgreSQL Upsert Using INSERT ON CONFLICT statement
March 27, 2024 - In this statement, the target can ... ... DO NOTHING – means do nothing if the row already exists in the table. DO UPDATE SET column_1 = value_1, .....
Codedamn
codedamn.com › news › sql
ON CONFLICT (Upsert) in PostgreSQL
May 19, 2023 - EXCLUDED is a system table that holds the row proposed for insertion initially. For instance: INSERT INTO products (product_id, product_name, quantity) VALUES (1, 'Apple', 50) ON CONFLICT (product_id) DO UPDATE SET quantity = EXCLUDED.quantity;