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

postgresql upsert postgresql-9.5

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

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

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

@foal post that as an answer, it is quite a useful alternative.

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

Braiam

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;

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

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

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.

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

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US