226

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, 'john@mail.com') 
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, 'john@mail.com')
Sebastian
  • 5,471
  • 5
  • 35
  • 53
  • 108
    Not a real answer but you can use slightly shortly notation: `INSERT INTO tablename (id, username, password, level, email) VALUES (1, 'John', 'qwerty', 5, 'john@mail.com') 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 Oct 13 '17 at 12:15
  • Another option is to use jsonb columns and that way you don't have to worry about columns – j will Jan 23 '18 at 21:41
  • @foal post that as an answer, it is quite a useful alternative. – rustyx Feb 08 '21 at 16:27
  • 2
    You don't need to update `id`, since it is the same (conflict field). That makes it a bit shorter. – milan Apr 29 '22 at 07:12

2 Answers2

269

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;
Braiam
  • 1
  • 11
  • 47
  • 78
Kristján
  • 18,165
  • 5
  • 50
  • 62
  • 4
    Can you expand on "but the exact issue in the insert did not cause the update"? – MrR Dec 18 '18 at 02:58
  • @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"? – Randall Jan 10 '19 at 16:30
  • 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. – pojo-guy Jan 10 '19 at 17:51
  • 3
    Perhaps it wasn't a conflict but another error e.g. field type error? – MrR Jan 31 '19 at 17:33
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, 'john@mail.com', 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.

Nate Bosscher
  • 397
  • 2
  • 10