In Mysql, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”.<\/p>\n
How to do it in PostgreSQL?<\/p>\n
A way to do an “UPSERT” in postgresql is to do two sequential UPDATE\/INSERT statements that are each designed to succeed or have no effect.<\/p>\n
UPDATE table SET field='C', field2='Z' WHERE id=3;\r\nINSERT INTO table (id, field, field2)\r\n SELECT 3, 'C', 'Z'\r\n WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);<\/pre>\nThe UPDATE will succeed if a row with “id=3” already exists, otherwise it has no effect.<\/p>\n
The INSERT will succeed only if row with “id=3” does not already exist.<\/p>\n
You can combine these two into a single string and run them both with a single SQL statement execute from your application. Running them together in a single transaction is highly recommended.<\/p>\n
With PostgreSQL 9.1 this can be achieved using a writeable CTE:<\/p>\n
WITH new_values (id, field1, field2) as (\r\n values\r\n (1, 'A', 'X'),\r\n (2, 'B', 'Y'),\r\n (3, 'C', 'Z')\r\n\r\n),\r\nupsert as\r\n(\r\n update mytable m\r\n set field1 = nv.field1,\r\n field2 = nv.field2\r\n FROM new_values nv\r\n WHERE m.id = nv.id\r\n RETURNING m.*\r\n)\r\nINSERT INTO mytable (id, field1, field2)\r\nSELECT id, field1, field2\r\nFROM new_values\r\nWHERE NOT EXISTS (SELECT 1\r\n FROM upsert up\r\n WHERE up.id = new_values.id)<\/pre>\nPostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL)<\/p>\n
INSERT INTO the_table (id, column_1, column_2) \r\nVALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')\r\nON CONFLICT (id) DO UPDATE \r\n SET column_1 = excluded.column_1, \r\n column_2 = excluded.column_2;\r\n<\/pre>\nYou can see this page for more about PostgreSQL “Upsert”: https:\/\/stackoverflow.com\/questions\/1109061\/insert-on-duplicate-update-postgresql<\/a><\/p>\n
See also:<\/p>\n
Upsert data from CSV to PostgreSQL <\/a>
\n Upsert data from TSV to PostgreSQL <\/a>
\n Upsert data from TXT to PostgreSQL <\/a>
\n Upsert data from Excel to PostgreSQL <\/a>
\n Upsert data from XML to PostgreSQL <\/a>
\n Upsert data from JSON to PostgreSQL <\/a>
\n Upsert data from SQL file to PostgreSQL <\/a><\/p>\nReplace (Update\/Insert) a row into other DB:<\/p>\n
In SQL Server,\u00a0https:\/\/www.withdata.com\/blog\/sql-server\/replace-update-or-insert-a-row-into-sql-server-table.html<\/a>\u00a0.<\/p>\n
In Oracle,\u00a0https:\/\/www.withdata.com\/blog\/oracle\/replace-update-or-insert-a-row-into-oracle-table-merge-into.html<\/a><\/p>\n
In DB2,\u00a0https:\/\/www.withdata.com\/blog\/db2\/replace-update-or-insert-a-row-into-db2-table-merge-into.html<\/a><\/p>\n