Postgres: 如果不存在则插入

Question

我正在使用Python编写postgres数据库：

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

但是由于我的一些行是相同的，所以我得到以下错误：

psycopg2.IntegrityError: duplicate key value  
  violates unique constraint "hundred_pkey"

如何编写“除非此行已存在，否则插入”SQL语句？

我见过这样复杂的语句：

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

但首先，这是不是太过分了，我需要什么，其次，我如何执行其中一个作为一个简单的字符串？

Answer 1

How can I write an 'INSERT unless this row already exists' SQL statement?

在PostgreSQL中有一种很好的方法来执行条件插入：

INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );

警告这种方法对于并发的写操作来说不是100%可靠的。在NOT EXISTS反半连接中的SELECT与INSERT本身之间有一个非常小的竞争条件。在这种情况下，它可以失败。

Answer 2

Postgres9.5（自2016-01-07发布）提供了一个"upsert"命令，也称为ON CONFLICT clause to INSERT：

INSERT ... ON CONFLICT DO NOTHING/UPDATE

它解决了在使用并发操作时可能遇到的许多微妙问题，这是其他一些答案提出的。

Answer 3

一种方法是创建一个非约束（没有唯一索引）表，将所有数据插入其中，并执行一个与在百表中插入不同的选择。

那么高的水平。我假设这三列在我的示例中都是不同的，所以对于步骤3，将表100中唯一列的NOT EXITS连接更改为only连接。

1. 创建临时表。参见文档here。

   CREATE TEMPORARY TABLE temp_data(name, name_slug, status);
   

2. 将数据插入临时表。

   INSERT INTO temp_data(name, name_slug, status); 
   

3. 向临时表添加任何索引。

4. 做主表插入。

   INSERT INTO hundred(name, name_slug, status) 
       SELECT DISTINCT name, name_slug, status
       FROM hundred
       WHERE NOT EXISTS (
           SELECT 'X' 
           FROM temp_data
           WHERE 
               temp_data.name          = hundred.name
               AND temp_data.name_slug = hundred.name_slug
               AND temp_data.status    = status
       );