Menu
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 can I write an 'INSERT unless this row already exists' SQL statement?
I've seen complex statements like this recommended:
IF EXISTS (SELECT * FROM invoices WHERE invoiceid = '12345') UPDATE invoices SET billed = 'TRUE' WHERE invoiceid = '12345' ELSE INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE') END IF But firstly, is this overkill for what I need, and secondly, how can I execute one of those as a simple string?
416
Firstly we have to mention the table name followed by column names (attributes) where we want to insert rows. Secondly, we must enter the values, separated by a comma after the VALUE clause. Finally, every value must be in the same order as the sequence of attribute lists is provided while creating a particular table.
In PostgreSQL, the INSERT statement is used to add new rows to a database table. As one creates a new database, it has no data initially. PostgreSQL provides the INSERT statement to insert data into the database. Syntax: INSERT INTO table(column1, column2, …) VALUES (value1, value2, …);
You need to join against the table, or use WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE ...) to make the INSERT INTO ... SELECT ... 's SELECT part return zero rows if the row already exists in the target table. Note, though, that that's still subject to race conditions.
PostgreSQL added the ON CONFLICT target action clause to the INSERT statement to support the upsert feature. In this statement, the target can be one of the following: (column_name) – a column name. ON CONSTRAINT constraint_name – where the constraint name could be the name of the UNIQUE constraint.
Postgres 9.5 (released since 2016-01-07) offers an "upsert" command, also known as an ON CONFLICT clause to INSERT:
INSERT ... ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
