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”.
How to do it in PostgreSQL?
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.
UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
The UPDATE will succeed if a row with “id=3” already exists, otherwise it has no effect.
The INSERT will succeed only if row with “id=3” does not already exist.
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.
With PostgreSQL 9.1 this can be achieved using a writeable CTE:
WITH new_values (id, field1, field2) as ( values (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ), upsert as ( update mytable m set field1 = nv.field1, field2 = nv.field2 FROM new_values nv WHERE m.id = nv.id RETURNING m.* ) INSERT INTO mytable (id, field1, field2) SELECT id, field1, field2 FROM new_values WHERE NOT EXISTS (SELECT 1 FROM upsert up WHERE up.id = new_values.id)
PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL)
INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2;
You can see this page for more about PostgreSQL “Upsert”: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
In SQL Server, http://www.withdata.com/ad/sql-server/replace-update-or-insert-a-row-into-sql-server-table.html
In Oracle, http://www.withdata.com/ad/oracle/replace-update-or-insert-a-row-into-oracle-table-merge-into.html
In DB2, http://www.withdata.com/ad/db2/replace-update-or-insert-a-row-into-db2-table-merge-into.html
In Sqlite, http://www.withdata.com/ad/sqlite/replace-update-or-insert-a-row-into-sqlite-table.html
Some PostgreSQL tools you can try: https://www.withdata.com/postgresql/