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"

如何编写除非此行已存在，否则请插入" 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

但是，首先，这对于我需要的功能来说是矫kill过正，其次，我如何才能将其中一个作为简单的字符串执行?

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年1月7日发布)提供了更新" 命令，也称为

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屋！