Use multiple conflict_target in ON CONFLICT clause

by

ON CONFLICT requires a unique index* to do the conflict detection. So you just need to create a unique index on both columns:

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, 'a', 'foo');
INSERT 0 1
t=# insert into t values (1, 'a', 'bar') on conflict (id, a) do update set b = 'bar';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

* In addition to unique indexes, you can also use exclusion constraints. These are a bit more general than unique constraints. Suppose your table had columns for id and valid_time (and valid_time is a tsrange), and you wanted to allow duplicate ids, but not for overlapping time periods. A unique constraint won’t help you, but with an exclusion constraint you can say “exclude new records if their id equals an old id and also their valid_time overlaps its valid_time.”

More Related Contents:

  1. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  2. Differentiate inserted and updated rows in UPSERT using system columns
  3. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  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. How does the search_path influence identifier resolution and the “current schema”
  10. How to use RETURNING with ON CONFLICT in PostgreSQL?
  11. How do you find the row count for all your tables in Postgres
  12. See and clear Postgres caches/buffers?
  13. postgresql duplicate key violates unique constraint
  14. gem install pg –with-pg-config works, bundle fails
  15. Find difference between timestamps in seconds in PostgreSQL
  16. Unable to connect PostgreSQL to remote database using pgAdmin
  17. How can I change a PostgreSQL user password?
  18. Docker wait for postgresql to be running
  19. I forgot the password I entered during PostgreSQL installation
  20. SQL query column does not exist error
  21. Postgresql enforce unique two-way combination of columns
  22. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  23. how to store PostgreSQL jsonb using SpringBoot + JPA?
  24. Orbeon Forms Postgres DB connection
  25. Heroku database restore issue
  26. user postgres launches process that takes all CPUs 100% usage
  27. PostgreSQL disable more output
  28. Postgres table column name restrictions?
  29. How do I get min, median and max from my query in postgresql?
  30. Syntax error at end of input in PostgreSQL

Leave a Comment