how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?

by

With PostgreSQL 9.5, this is now native functionality (like MySQL has had for several years):

INSERT … ON CONFLICT DO NOTHING/UPDATE (“UPSERT”)

9.5 brings support for “UPSERT” operations.
INSERT is extended to accept an ON CONFLICT DO UPDATE/IGNORE clause. This clause specifies an alternative action to take in the event of a would-be duplicate violation.

…

Further example of new syntax: 

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

More Related Contents:

  1. Why the “Group BY” by month didn’t work? [closed]
  2. Postgres: INSERT if does not exist already
  3. Truncating all tables in a Postgres database
  4. How can I drop all the tables in a PostgreSQL database?
  5. How to change PostgreSQL user password?
  6. How to get the count of current month Sunday’s in psql?
  7. quick random row selection in Postgres
  8. Time zone names with identical properties yield different result when applied to timestamp
  9. Permission denied for relation
  10. CONSTRAINT to check values from a remotely related table (via join etc.)
  11. Can I automatically create a table in PostgreSQL from a csv file with headers?
  12. PostgreSQL query very slow with limit 1
  13. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  14. Postgres query optimization (forcing an index scan)
  15. psql: FATAL: database “” does not exist
  16. Difference between timestamps with/without time zone in PostgreSQL
  17. Omitting the double quote to do query on PostgreSQL
  18. Storing Images in PostgreSQL
  19. Psql could not connect to server: No such file or directory, 5432 error?
  20. Strange behaviour in Postgresql
  21. In PostgreSQL, how to insert data with COPY command?
  22. Cursor based records in PostgreSQL
  23. How to add column if not exists on PostgreSQL?
  24. Maximum characters in labels (table names, columns etc)
  25. Docker – Postgres and pgAdmin 4 : Connection refused
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment