upsert

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

by

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1, ‘John’, ‘qwerty’, 5, ‘[email protected]’) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, username=EXCLUDED.username, password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email Is … Read more

How to use RETURNING with ON CONFLICT in PostgreSQL?

by

I have the following UPSERT in PostgreSQL 9.5: INSERT INTO chats (“user”, “contact”, “name”) VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT(“user”, “contact”) DO NOTHING RETURNING id; If there are no conflicts it returns something like this: ———- | id | ———- 1 | 50 | ———- 2 | 51 | ———- But if … Read more

How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?

by

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT … ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation. Given that PostgreSQL doesn’t support it directly (before pg 9.5), how do you do this? Consider the following: CREATE TABLE testtable ( id … Read more

Postgres: INSERT if does not exist already

by

I’m using Python to write to a postgres database: sql_string = “INSERT INTO hundred (name,name_slug,status) VALUES (” sql_string += hundred + “, ‘” + hundred_slug + “‘, ” + status + “);” cursor.execute(sql_string) But because some of my rows are identical, I get the following error: psycopg2.IntegrityError: duplicate key value violates unique constraint “hundred_pkey” How … Read more

Solutions for INSERT OR UPDATE on SQL Server

by

Assume a table structure of MyTable(KEY, datafield1, datafield2…). Often I want to either update an existing record, or insert a new record if it doesn’t exist. Essentially: IF (key exists) run update command ELSE run insert command What’s the best performing way to write this? 23 s 23 don’t forget about transactions. Performance is good, … Read more

Insert, on duplicate update in PostgreSQL?

by

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); I’ve now switched over to PostgreSQL and apparently this … Read more