I’m using Python to write to a postgres database: But because some of my rows are identical, I get the following error: How can I write an ‘INSERT unless this row already exists’ SQL statement? I’ve seen complex statements like this recommended: But firstly, is this overkill for what I need, and secondly, how can … Read more
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: What’s the best performing way to write this?
Use INSERT … ON DUPLICATE KEY UPDATE QUERY:
Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the default value: GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to … Read more
9.5 and newer: PostgreSQL 9.5 and newer support INSERT … ON CONFLICT (key) DO UPDATE (and ON CONFLICT (key) DO NOTHING), i.e. upsert. Comparison with ON DUPLICATE KEY UPDATE. Quick explanation. For usage see the manual – specifically the conflict_action clause in the syntax diagram, and the explanatory text. Unlike the solutions for 9.4 and older that are given below, this feature works with … Read more