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]')

Oulu

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 – Fondle

Another option is to use jsonb columns and that way you don't have to worry about columns – Meara

@Fondle post that as an answer, it is quite a useful alternative. – Canicula

You don't need to update id, since it is the same (conflict field). That makes it a bit shorter. – Knap

Answer 1

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;

Redouble

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

@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"? – Donee

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. – Trunk

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

@Redouble 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? – Syphon

Answer 2

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.

Flounder

Upvote for pointing out how to reference the existing row – Straus