Postgresql Insert on Conflict Update (Upsert) Use All Excluded Values
When You Are Upserting a Row (Postgresql >= 9.5), and You Want the Possible Insert to Be Exactly the Same as the Possible Update, You Can Write It Like This...
When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this:
INSERT INTO tablename (id, username, password, level, email)
VALUES (1, 'John', 'qwerty', 5, '')
ON CONFLICT (id) DO UPDATE SET
id=EXCLUDED.id, username=EXCLUDED.username,
password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email
Is there a shorter way? To just say: use all the EXCLUDE values.
In SQLite I used to do :
INSERT OR REPLACE INTO tablename (id, user, password, level, email)
VALUES (1, 'John', 'qwerty', 5, '')
1 Answer
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;
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
