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 OverflowPostgres 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;
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.
Upsert always updates postgreSQL - Database Administrators Stack Exchange
on Conflict Do Update All!!
Do I need to specify a primary key for the ON CONFLICT statement?
How to do onConflict multiple in Postgres?
Videos
According to the Postgres documentation https://www.postgresql.org/docs/current/sql-insert.html the INSERT INTO statement has two WHERE conditions:
ON CONFLICT ... WHERE index_predicatein theconflict_targetpart. 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. Theindex_predicateallows 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.DO UPDATE SET ... WHERE conditionin theconflict_actionpart. Only rows for which thisconditionexpression 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.
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.