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

Question

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, '[email protected]') 
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, '[email protected]')

Not a real answer but you can use slightly shortly notation: INSERT INTO tablename (id, username, password, level, email) VALUES (1, 'John', 'qwerty', 5, '[email protected]') ON CONFLICT (id) DO UPDATE SET (username, password, level, email) = (EXCLUDED.username, EXCLUDED.password, EXCLUDED.level, EXCLUDED.email). Almost the same, but easy to copy/paste/manage the column list — foal
– foal, Commented Oct 13, 2017 at 12:15
Another option is to use jsonb columns and that way you don't have to worry about columns — j will
– j will, Commented Jan 23, 2018 at 21:41
@foal post that as an answer, it is quite a useful alternative. — rustyx
– rustyx, Commented Feb 8, 2021 at 16:27
You don't need to update id, since it is the same (conflict field). That makes it a bit shorter. — milan
– milan, Commented Apr 29, 2022 at 7:12

Braiam · Accepted Answer · 2021-09-16 14:24:25Z

307

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;

edited Sep 16, 2021 at 14:24

Braiam

4,49411 gold badges50 silver badges83 bronze badges

answered Apr 1, 2016 at 15:29

Kristján

19k5 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MrR Over a year ago

Can you expand on "but the exact issue in the insert did not cause the update"?

Randall Over a year ago

@pojo-guy - I don't think you saw the question from MrR - Can you expand on "but the exact issue in the insert did not cause the update"?

pojo-guy Over a year ago

When you attempt to use insert ... on update in postgresql, the results are different under some specific circumstances than a merge. The case I ran into was rather obscure and specific, but it was repeatable. It's been a few months, so I can't give any more rightnow.

MrR Over a year ago

Perhaps it wasn't a conflict but another error e.g. field type error?

Satish Patro Over a year ago

@Kristján does the users.level takes from what is in DB (let's assume 5) or is it based on the corresponding value given for the level field which is 0 in this case? If there is conflict what will be the new value 6 to 1?

Nate Bosscher · Accepted Answer · 2023-07-13 15:46:27Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related