Postgres:如果不存在则插入 [英] Postgres: INSERT if does not exist already
问题描述
我正在使用 Python 写入 postgres 数据库:
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"
如何编写INSERT,除非该行已存在"SQL 语句?
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?
推荐答案
Postgres 9.5(自 2016-01-07 发布)提供了一个 "upsert" 命令,也称为 ON CONFLICT 子句插入:
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.
这篇关于Postgres:如果不存在则插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!