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;
Answer from Kristján on Stack Overflow
🌐
PostgreSQL
postgresql.org › docs › current › sql-insert.html
PostgreSQL: Documentation: 18: INSERT
February 26, 2026 - The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ON CONFLICT DO UPDATE ...
Discussions

Upsert always updates postgreSQL - Database Administrators Stack Exchange
INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) ON CONFLICT (id) WHERE updated IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP DO UPDATE SET version = books.version + 1, updated = CURRENT_TIMESTAMP; however even if the where clause is not true, the row is updated. Here's example: https://dbfiddle.uk/CPHvZDm3 · I can't understand what is wrong here. ... According to the Postgres ... More on dba.stackexchange.com
🌐 dba.stackexchange.com
November 13, 2022
on Conflict Do Update All!!
and i have to list all the column names. Yes, there is no way around it. More on reddit.com
🌐 r/PostgreSQL
12
0
December 22, 2022
Do I need to specify a primary key for the ON CONFLICT statement?
Ok, so many things wrong here. First off, tell me you're not storing passwords in the clear? Even if a demo, unless caveating this is throw away code, encrypt the password. Second, are you saying other tables that track user activity are going to have a foreign key column of email_phone to the credentials table? Don't do that. Third, you don't update the key column in an upset/merge statement. If you go with email_phone as a primary key in that table (or, if there is an id PK col not shown here, a unique index on email_phone), take out the email_phone = excluded.email_phone. Consider instead: person table with an INT PK of id. The credentials table then has: id (Pk), person_id (FK->persons), email_phone, password (ENCRYPTED!!!), last_login_at (TIMESTAMPTZ), is_locked INT (some say BOOLEAN ok, but I never use those nor BIT), locked_at (TIMESTAMPTZ), locked_reason VARCHAR. You add a unique index on person_id and that is what you use in the ON CONFLICT. Some will set password to NULL to disable the account. That is OK-ish. I say better to be explicit. More on reddit.com
🌐 r/SQL
9
5
November 24, 2022
How to do onConflict multiple in Postgres?
Not possible, see the documentation: https://www.postgresql.org/docs/12/sql-insert.html . Exactly same question: https://dba.stackexchange.com/questions/206185/multiple-on-conflict-targets More on reddit.com
🌐 r/PostgreSQL
3
2
March 3, 2020
🌐
Alibaba Cloud Community
alibabacloud.com › blog › use-of-the-postgresql-upsert-insert-on-conflict-do-function_596027
Use of the PostgreSQL Upsert (INSERT ON CONFLICT DO) Function - Alibaba Cloud Community
March 25, 2020 - Upsert (INSERT ON CONFLICT DO) is a new function of PostgreSQL 9.5. When a constraint error occurs during data insertion, data insertion is rolled back or changed to update.
🌐
PostgreSQL
postgresql.org › docs › current › logical-replication-conflicts.html
PostgreSQL: Documentation: 18: 29.7. Conflicts
February 26, 2026 - ... Logical replication behaves ... in that the data will be updated even if it was changed locally on the subscriber node. If incoming data violates any constraints the replication will stop. This is referred to as a conflict. When replicating UPDATE or DELETE operations, missing data is also considered as a conflict, but does not result in an error and such operations will simply be skipped...
🌐
PostgreSQL
postgresql.org › files › developer › concurrency.pdf pdf
PostgreSQL Concurrency Issues 1 PostgreSQL Concurrency Issues Tom Lane
Now the total is only $800; oops. Neither client saw the other’s · uncommitted change, so the constraint checks both succeeded. ... SUM will not miss any uncommitted updates. Furthermore, any new writer · will have to wait at his LOCK command until we’ve committed our own · update, and then he’ll see it when he does his SUM.
🌐
Macsqlclient
macsqlclient.com › home › blog › postgresql upsert: insert on conflict explained
PostgreSQL UPSERT: INSERT ON CONFLICT Explained
July 8, 2022 - INSERT INTO settings (key, value) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value WHERE settings.value <> EXCLUDED.value; This avoids unnecessary writes when the value has not actually changed.
Find elsewhere
🌐
Medium
medium.com › @linglijunmail › php5-and-postgresql-insert-or-update-on-conflict-6ab11263d028
PHP5 and PostgreSQL: Insert or Update on Conflict | by Jason Anderson | Medium
July 20, 2024 - The basic syntax for PostgreSQL’s INSERT ON CONFLICT statement is: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON CONFLICT (conflict_column) DO UPDATE SET column1 = value1, column2 = value2, ...;
🌐
Prisma
prisma.io › dataguide › postgresql › inserting-and-modifying-data › insert-on-conflict
Using "INSERT ON CONFLICT" to Upsert and Modify Data in PostgreSQL
In essence, this action makes no changes, but suppresses the error that would normally occur if you tried to insert a row that violates a condition. DO UPDATE: This tells PostgreSQL that you want to update the row that is already in the table.
🌐
PostgreSQL
postgresql.org › message-id › CAM3SWZTEODEJLz82LK4eF2HYX+qEKrbc8-Vtq3_-aOf6kRSfiA@mail.gmail.com
PostgreSQL: INSERT ... ON CONFLICT {UPDATE | IGNORE}
August 28, 2014 - There are some restrictions on what this auxiliary update may do, but FWIW there are considerably fewer than those that the equivalent MySQL or SQLite feature imposes on their users. All of the following SQL queries are valid with the patch applied: -- Nesting within wCTE: WITH t AS ( INSERT INTO z SELECT i, 'insert' FROM generate_series(0, 16) i ON CONFLICT UPDATE SET v = v || 'update' -- use of operators/functions in targetlist RETURNING * -- only projects inserted tuples, never updated tuples ) SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
Top answer
1 of 2
2

According to the Postgres documentation https://www.postgresql.org/docs/current/sql-insert.html the INSERT INTO statement has two WHERE conditions:

  1. ON CONFLICT ... WHERE index_predicate in the conflict_target part. Postgres needs to understand on what unique index it should react to conflicts, especially if you have multiple unique indexes on a table. You can specify the exact index name, or you can specify column name and other parameters and Postgres will try to find ("infer") corresponding index. The index_predicate allows to infer partial unique indexes (i.e. unique indexes that contain information only about part of the table), but any indexes that satisfy the predicate (not only partial indexes) can be inferred.
  2. DO UPDATE SET ... WHERE condition in the conflict_action part. Only rows for which this condition expression returns true will be updated.

So you need to move you WHERE clause from the ON CONFLICT block to the DO UPDATE SET block:

CREATE TABLE books (
    id int4 NOT NULL,
    version int8 NOT NULL,
    updated timestamp NULL,
    CONSTRAINT books_pkey PRIMARY KEY (id)
);

INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP)
ON CONFLICT (id) 
DO UPDATE SET version = books.version + 1, updated = CURRENT_TIMESTAMP
WHERE books.version IS NULL OR books.updated + INTERVAL '2min' < CURRENT_TIMESTAMP;

Upd: In your case the version cannot be NULL and you don't need the books.version IS NULL condition. In case you need to allow NULLs be aware that NULL + 1 will be NULL (thanks @dwhitemv for noticing) so you will need to modify the SET statement using COALESCE or conditions.

2 of 2
2

You need to move the WHERE clause to after the DO UPDATE:

INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP)
ON CONFLICT (id) 
DO UPDATE SET version = books.version + 1, 
updated = CURRENT_TIMESTAMP 
WHERE version IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP;

That said, version will always be set to NULL if it is already NULL as NULL+1=NULL.

🌐
CYBERTEC PostgreSQL
cybertec-postgresql.com › home › dealing with streaming replication conflicts in postgresql
Replication conflicts in PostgreSQL and how to deal with them | CYBERTEC PostgreSQL | Services & Support
March 5, 2024 - PostgreSQL takes such a lock for ... a lock on a table that a query uses, we have a lock conflict. One way to reduce the need for VACUUM is to use HOT updates....
🌐
Readthedocs
django-postgres-extra.readthedocs.io › en › latest › conflict_handling.html
Conflict handling — django-postgres-extra documentation
The second parameter of on_conflict() allows you to specify that should happen. ... If the row does not exist, insert a new one. If the row exists, update it. This is also known as a “upsert”. Optionally, a condition can be added. PostgreSQL will then only apply the update if the condition ...
🌐
Brianchildress
brianchildress.co › blog › upsert-in-postgres-with-on-conflct
Upsert in PostgreSQL Using ON CONFLICT | Blog
February 19, 2020 - ON CONFLICT (email_address) DO UPDATE SET last_updated = Date.now() ON CONFLICT, introduced in Postgres 9.5, is the Postgres implementation of Upsert. Here we’re evaluating if there is another row with an equal value to a constraint called ...
🌐
CommandPrompt Inc.
commandprompt.com › education › postgresql-upsert-using-insert-on-conflict-statement
PostgreSQL Upsert Using INSERT ON CONFLICT Statement — CommandPrompt Inc.
September 23, 2022 - This is because the upsert feature ... new record into the targeted table. In PostgreSQL, the upsert feature can be implemented with the aid of the INSERT ON CONFLICT statement....
🌐
Tistory
mine-it-record.tistory.com › 342
[PostgreSQL] 데이터 있으면 UPDATE 없으면 INSERT (INSERT INTO ~ ON CONFLICT DO UPDATE)
June 11, 2022 - INSERT INTO EMPLOYEE (EMP_SN, SALARY, NAME, DEPT, ETC) VALUES (#{empSn}, #{salary}, #{name}, #{dept}, #{etc}) ON CONFLICT (EMP_SN) DO UPDATE SET EMP_SN = #{empSn}, SALARY = #{salary}, NAME = #{name}, DEPT = #{dept}, ETC = #{etc} INSERT INTO EMPLOYEE (EMP_SN, SALARY, NAME, DEPT, ETC) VALUES (2, 2400, 'mine', 'it', 'record') ON CONFLICT ON CONSTRAINT mine_it_record_key DO NOTHING · 해당 upsert 구문은 PostgreSQL 9.5 이상부터 사용 가능하다고 한다.
🌐
Beekeeper Studio
beekeeperstudio.io › blog › postgres-on-conflict
PostgreSQL ON CONFLICT Walkthrough | Beekeeper Studio
January 7, 2023 - You can also specify which columns ... clause. In summary, the ON CONFLICT clause in PostgreSQL allows you to handle unique constraint violations in a controlled manner....
🌐
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 - That Upsert query in Postgres can be achieved with INSERT ON CONFLICT UPDATE as seen below: INSERT INTO quote (id, quote, author) VALUES (3, 'First, solve the problem. Then, write the code1.', 'John Johnson1') ON CONFLICT (id) DO UPDATE SET quote = excluded.quote, author = excluded.author, updated_at = DEFAULT RETURNING *;
🌐
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
1 month ago - Prevent duplicate-row errors in AnalyticDB for PostgreSQL using INSERT ON CONFLICT. Master DO NOTHING, DO UPDATE SET, and partial-column UPSERT patterns—with SQL examples covering Beam table full-row conflict resolution to ensure data consistency.
🌐
Ankur Rathore
ankurrathore.net.in › posts › understanding_upsert_onconflict_sql
Understanding PostgreSQL UPSERT with ON CONFLICT · Ankur Rathore
December 12, 2024 - PostgreSQL provides a powerful feature called ON CONFLICT for handling duplicate key violations during data insertion. In this post, we’ll explore the following query: INSERT INTO grp_nodes (group_id, node_id, x, y) VALUES (1, 101, 10.0, 20.0), (2, 102, 30.0, 40.0), (3, 103, 50.0, 60.0) ON CONFLICT (group_id, node_id) DO UPDATE SET x = EXCLUDED.x, y = EXCLUDED.y;