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

by

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;

More Related Contents:

  1. Use multiple conflict_target in ON CONFLICT clause
  2. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  3. Differentiate inserted and updated rows in UPSERT using system columns
  4. Insert, on duplicate update in PostgreSQL?
  5. How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
  6. Postgres: INSERT if does not exist already
  7. serial in postgres is being increased even though I added on conflict do nothing
  8. Return rows from INSERT with ON CONFLICT without needing to update
  9. PostgreSQL LIKE query performance variations
  10. Calculating and saving space in PostgreSQL
  11. I forgot the password I entered during postgres installation
  12. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  13. How do I efficiently select the previous non-null value?
  14. How do I do large non-blocking updates in PostgreSQL?
  15. How to configure PostgreSQL to accept all incoming connections
  16. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  17. Dynamic SQL (EXECUTE) as condition for IF statement
  18. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  19. Can’t connect to Postgresql on port 5432
  20. Spark Dataframes UPSERT to Postgres Table
  21. PostgreSQL Error: Relation already exists
  22. How to make a select with array contains value clause in psql
  23. Optimize Postgres query on timestamp range
  24. Use variable set by psql meta-command inside of DO block
  25. Sorting array elements
  26. input file appears to be a text format dump. Please use psql
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment