postgresql Postgres：如果不存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4069718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: INSERT if does not exist already
提问by AP257
I'm using Python to write to a postgres database:
我正在使用 Python 写入 postgres 数据库：
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?
如何编写“除非此行已存在”的 SQL 语句？
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?
但首先，这对于我需要的东西来说是否太过分了，其次，我如何将其中一个作为一个简单的字符串执行？
回答by Arie
Postgres 9.5 (released since 2016-01-07) offers an "upsert"command, also known as an ON CONFLICT clause to INSERT:
Postgres 9.5（自 2016-01-07 发布）提供了一个“upsert”命令，也称为INSERT的ON CONFLICT 子句：
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.
它解决了您在使用并发操作时可能遇到的许多微妙问题，其他一些答案提出了这些问题。
回答by John Doe
How can I write an 'INSERT unless this row already exists' SQL statement?
如何编写“除非此行已存在”的 SQL 语句？
There is a nice way of doing conditional INSERT in PostgreSQL:
在 PostgreSQL 中有一个很好的条件插入方法：
INSERT INTO example_table
(id, name)
SELECT 1, 'John'
WHERE
NOT EXISTS (
SELECT id FROM example_table WHERE id = 1
);
CAVEATThis approach is not 100% reliable for concurrentwrite operations, though. There is a very tiny race condition between the SELECTin the NOT EXISTSanti-semi-join and the INSERTitself. It canfail under such conditions.
但是，这种方法对于并发写入操作不是 100% 可靠的。还有就是一间非常小的竞争条件SELECT在NOT EXISTS反半连接和INSERT本身。在这种情况下它可能会失败。
回答by Kuberchaun
One approach would be to create a non-constrained (no unique indexes) table to insert all your data into and do a select distinct from that to do your insert into your hundred table.
一种方法是创建一个非约束（无唯一索引）表以将所有数据插入到其中并执行与此不同的选择以将插入到您的百表中。
So high level would be. I assume all three columns are distinct in my example so for step3 change the NOT EXITS join to only join on the unique columns in the hundred table.
那么高的水平。我假设在我的示例中所有三列都是不同的，因此对于 step3，将 NOT EXITS 连接更改为仅连接到 100 表中的唯一列。
Create temporary table. See docs here.
CREATE TEMPORARY TABLE temp_data(name, name_slug, status);INSERT Data into temp table.
INSERT INTO temp_data(name, name_slug, status);Add any indexes to the temp table.
Do main table insert.
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 );
创建临时表。请参阅此处的文档。
CREATE TEMPORARY TABLE temp_data(name, name_slug, status);将数据插入临时表。
INSERT INTO temp_data(name, name_slug, status);将任何索引添加到临时表。
做主表插入。
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 );
回答by Quassnoi
Unfortunately, PostgreSQLsupports neither MERGEnor ON DUPLICATE KEY UPDATE, so you'll have to do it in two statements:
不幸的是，PostgreSQL既不支持MERGE也不支持ON DUPLICATE KEY UPDATE，所以你必须用两个语句来做：
UPDATE invoices
SET billed = 'TRUE'
WHERE invoices = '12345'
INSERT
INTO invoices (invoiceid, billed)
SELECT '12345', 'TRUE'
WHERE '12345' NOT IN
(
SELECT invoiceid
FROM invoices
)
You can wrap it into a function:
你可以把它包装成一个函数：
CREATE OR REPLACE FUNCTION fn_upd_invoices(id VARCHAR(32), billed VARCHAR(32))
RETURNS VOID
AS
$$
UPDATE invoices
SET billed =
WHERE invoices = ;
INSERT
INTO invoices (invoiceid, billed)
SELECT ,
WHERE NOT IN
(
SELECT invoiceid
FROM invoices
);
$$
LANGUAGE 'sql';
and just call it:
只需调用它：
SELECT fn_upd_invoices('12345', 'TRUE')
回答by crististm
You can make use of VALUES - available in Postgres:
您可以使用 VALUES - 在 Postgres 中可用：
INSERT INTO person (name)
SELECT name FROM person
UNION
VALUES ('Bob')
EXCEPT
SELECT name FROM person;
回答by ktr
I know this question is from a while ago, but thought this might help someone. I think the easiest way to do this is via a trigger. E.g.:
我知道这个问题是很久以前的问题，但我认为这可能会对某人有所帮助。我认为最简单的方法是通过触发器。例如：
Create Function ignore_dups() Returns Trigger
As $$
Begin
If Exists (
Select
*
From
hundred h
Where
-- Assuming all three fields are primary key
h.name = NEW.name
And h.hundred_slug = NEW.hundred_slug
And h.status = NEW.status
) Then
Return NULL;
End If;
Return NEW;
End;
$$ Language plpgsql;
Create Trigger ignore_dups
Before Insert On hundred
For Each Row
Execute Procedure ignore_dups();
Execute this code from a psql prompt (or however you like to execute queries directly on the database). Then you can insert as normal from Python. E.g.:
从 psql 提示符执行此代码（或者您喜欢直接在数据库上执行查询）。然后您可以从 Python 正常插入。例如：
sql = "Insert Into hundreds (name, name_slug, status) Values (%s, %s, %s)"
cursor.execute(sql, (hundred, hundred_slug, status))
Note that as @Thomas_Wouters already mentioned, the code above takes advantage of parameters rather than concatenating the string.
请注意，正如@Thomas_Wouters 已经提到的，上面的代码利用了参数而不是连接字符串。
回答by Ritesh Jha
There is a nice way of doing conditional INSERT in PostgreSQL using WITH query: Like:
有一种使用 WITH 查询在 PostgreSQL 中进行条件插入的好方法：例如：
WITH a as(
select
id
from
schema.table_name
where
column_name = your_identical_column_value
)
INSERT into
schema.table_name
(col_name1, col_name2)
SELECT
(col_name1, col_name2)
WHERE NOT EXISTS (
SELECT
id
FROM
a
)
RETURNING id
回答by tuanngocptn
This is exactly the problem I face and my version is 9.5
这正是我面临的问题，我的版本是 9.5
And I solve it with SQL query below.
我用下面的 SQL 查询解决了它。
INSERT INTO example_table (id, name)
SELECT 1 AS id, 'John' AS name FROM example_table
WHERE NOT EXISTS(
SELECT id FROM example_table WHERE id = 1
)
LIMIT 1;
Hope that will help someone who has the same issue with version >= 9.5.
希望对版本>= 9.5 有相同问题的人有所帮助。
Thanks for reading.
谢谢阅读。
回答by Pavel Francírek
INSERT .. WHERE NOT EXISTS is good approach. And race conditions can be avoided by transaction "envelope":
INSERT .. WHERE NOT EXISTS 是一个好方法。事务“信封”可以避免竞争条件：
BEGIN;
LOCK TABLE hundred IN SHARE ROW EXCLUSIVE MODE;
INSERT ... ;
COMMIT;
回答by Pavel Francírek
It's easy with rules:
规则很简单：
CREATE RULE file_insert_defer AS ON INSERT TO file
WHERE (EXISTS ( SELECT * FROM file WHERE file.id = new.id)) DO INSTEAD NOTHING
But it fails with concurrent writes ...
但是并发写入失败了......
