我正在使用 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
但首先,这是否符合我的需要,其次,我怎样才能将其中一个作为简单的字符串执行?
Postgres 9.5(自 2016-01-07 发布)提供了一个 "upsert" 命令,也称为 ON CONFLICT clause to INSERT:
INSERT ... ON CONFLICT DO NOTHING/UPDATE
它解决了您在使用并发操作时可能遇到的许多微妙问题,其他一些答案也提出了这些问题。
如何编写“插入,除非该行已经存在”的 SQL 语句?
在 PostgreSQL 中有一个很好的方法来进行条件插入:
INSERT INTO example_table
(id, name)
SELECT 1, 'John'
WHERE
NOT EXISTS (
SELECT id FROM example_table WHERE id = 1
);
CAVEAT 但是,对于并发 写入操作,这种方法并不是 100% 可靠的。 NOT EXISTS
反半联接中的 SELECT
和 INSERT
本身之间存在非常小的竞争条件。它可能在这种情况下失败。
RETURNS id
一起使用,例如获取 id
是否已插入?
RETURNING id
,如果没有插入任何行,它将返回新的行 ID 或不返回任何内容。
一种方法是创建一个不受约束的(没有唯一索引)表来将所有数据插入并执行与该表不同的选择以插入到一百个表中。
那么高的水平。我假设在我的示例中所有三列都是不同的,因此对于第 3 步,将 NOT EXITS 连接更改为仅连接 100 表中的唯一列。
创建临时表。请参阅此处的文档。创建临时表 temp_data(name, name_slug, status);将数据插入临时表。插入到 temp_data(名称,name_slug,状态);将任何索引添加到临时表。做主表插入。插入百(名称,name_slug,状态)从不存在的百中选择不同的名称,name_slug,状态(从 temp_data 中选择“X”,其中 temp_data.name = 百名和 temp_data.name_slug = 百名.name_slug 和 temp_data.status =地位 );
SELECT name,name_slug,status
或 *
SELECT DISTINCT name, name_slug, status FROM temp_data
吗?
INSERT
和 SELECT
之间的竞争条件吗?
不幸的是,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
)
您可以将其包装成一个函数:
CREATE OR REPLACE FUNCTION fn_upd_invoices(id VARCHAR(32), billed VARCHAR(32))
RETURNS VOID
AS
$$
UPDATE invoices
SET billed = $2
WHERE invoices = $1;
INSERT
INTO invoices (invoiceid, billed)
SELECT $1, $2
WHERE $1 NOT IN
(
SELECT invoiceid
FROM invoices
);
$$
LANGUAGE 'sql';
并称之为:
SELECT fn_upd_invoices('12345', 'TRUE')
INSERT INTO hundred (name, name_slug, status) SELECT 'Chichester', 'chichester', NULL WHERE 'Chichester' NOT IN (SELECT NAME FROM hundred);
任意次数,它会不断插入行。
CREATE TABLE hundred (name TEXT, name_slug TEXT, status INT); INSERT INTO hundred (name, name_slug, status) SELECT 'Chichester', 'chichester', NULL WHERE 'Chichester' NOT IN (SELECT NAME FROM hundred); INSERT INTO hundred (name, name_slug, status) SELECT 'Chichester', 'chichester', NULL WHERE 'Chichester' NOT IN (SELECT NAME FROM hundred); SELECT * FROM hundred
。有一个记录。
ON DUPLICATE KEY UPDATE
。它被称为 ON CONFLICT (column) DO UPDATE SET
这正是我面临的问题,我的版本是 9.5
我用下面的 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;
希望这将帮助那些与版本> = 9.5有相同问题的人。
谢谢阅读。
您可以使用 VALUES - 在 Postgres 中可用:
INSERT INTO person (name)
SELECT name FROM person
UNION
VALUES ('Bob')
EXCEPT
SELECT name FROM person;
我知道这个问题来自不久前,但认为这可能对某人有所帮助。我认为最简单的方法是通过触发器。例如:
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();
从 psql 提示符执行此代码(或者您喜欢直接在数据库上执行查询)。然后你可以像往常一样从 Python 插入。例如:
sql = "Insert Into hundreds (name, name_slug, status) Values (%s, %s, %s)"
cursor.execute(sql, (hundred, hundred_slug, status))
请注意,正如@Thomas_Wouters 已经提到的,上面的代码利用了参数而不是连接字符串。
使用 WITH 查询在 PostgreSQL 中进行条件 INSERT 有一个很好的方法:例如:
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
我们可以使用 upsert 简化查询
insert into invoices (invoiceid, billed)
values ('12345', 'TRUE')
on conflict (invoiceid) do
update set billed=EXCLUDED.billed;
INSERT .. WHERE NOT EXISTS 是一个好方法。并且可以通过事务“信封”来避免竞争条件:
BEGIN;
LOCK TABLE hundred IN SHARE ROW EXCLUSIVE MODE;
INSERT ... ;
COMMIT;
使用规则很容易:
CREATE RULE file_insert_defer AS ON INSERT TO file
WHERE (EXISTS ( SELECT * FROM file WHERE file.id = new.id)) DO INSTEAD NOTHING
但它因并发写入而失败......
投票最多的方法(来自 John Doe)确实对我有用,但在我的情况下,从预期的 422 行中我只得到 180 行。我找不到任何错误并且根本没有错误,所以我寻找了不同的简单的方法。
在 SELECT
之后使用 IF NOT FOUND THEN
非常适合我。
(在 PostgreSQL Documentation 中描述)
文档中的示例:
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
psycopgs 游标类具有属性 rowcount。
此只读属性指定最后一次执行 *() 产生的行数(对于 DQL 语句,如 SELECT)或受影响的行数(对于 DML 语句,如 UPDATE 或 INSERT)。
因此,您可以先尝试 UPDATE 并仅在 rowcount 为 0 时才尝试 INSERT。
但是根据数据库中的活动级别,您可能会遇到 UPDATE 和 INSERT 之间的竞争条件,其中另一个进程可能会在此期间创建该记录。
您的“百”列似乎被定义为主键,因此必须是唯一的,但事实并非如此。问题不在于,而在于您的数据。
我建议你插入一个 id 作为序列类型来处理主键
如果您说您的许多行是相同的,您将多次检查。您可以发送它们,数据库将使用 ON CONFLICT 子句确定是否插入它,如下所示
INSERT INTO Hundred (name,name_slug,status) VALUES ("sql_string += hundred
+",'" + hundred_slug + "', " + status + ") ON CONFLICT ON CONSTRAINT
hundred_pkey DO NOTHING;" cursor.execute(sql_string);
我一直在寻找类似的解决方案,试图找到在 PostgreSQL 和 HSQLDB 中工作的 SQL。 (HSQLDB 使这变得困难。)以您的示例为基础,这是我在其他地方找到的格式。
sql = "INSERT INTO hundred (name,name_slug,status)"
sql += " ( SELECT " + hundred + ", '" + hundred_slug + "', " + status
sql += " FROM hundred"
sql += " WHERE name = " + hundred + " AND name_slug = '" + hundred_slug + "' AND status = " + status
sql += " HAVING COUNT(*) = 0 );"
这是一个通用的 python 函数,它给定一个表名、列和值,为 postgresql 生成等效的 upsert。
导入json
def upsert(table_name, id_column, other_columns, values_hash):
template = """
WITH new_values ($$ALL_COLUMNS$$) as (
values
($$VALUES_LIST$$)
),
upsert as
(
update $$TABLE_NAME$$ m
set
$$SET_MAPPINGS$$
FROM new_values nv
WHERE m.$$ID_COLUMN$$ = nv.$$ID_COLUMN$$
RETURNING m.*
)
INSERT INTO $$TABLE_NAME$$ ($$ALL_COLUMNS$$)
SELECT $$ALL_COLUMNS$$
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.$$ID_COLUMN$$ = new_values.$$ID_COLUMN$$)
"""
all_columns = [id_column] + other_columns
all_columns_csv = ",".join(all_columns)
all_values_csv = ','.join([query_value(values_hash[column_name]) for column_name in all_columns])
set_mappings = ",".join([ c+ " = nv." +c for c in other_columns])
q = template
q = q.replace("$$TABLE_NAME$$", table_name)
q = q.replace("$$ID_COLUMN$$", id_column)
q = q.replace("$$ALL_COLUMNS$$", all_columns_csv)
q = q.replace("$$VALUES_LIST$$", all_values_csv)
q = q.replace("$$SET_MAPPINGS$$", set_mappings)
return q
def query_value(value):
if value is None:
return "NULL"
if type(value) in [str, unicode]:
return "'%s'" % value.replace("'", "''")
if type(value) == dict:
return "'%s'" % json.dumps(value).replace("'", "''")
if type(value) == bool:
return "%s" % value
if type(value) == int:
return "%s" % value
return value
if __name__ == "__main__":
my_table_name = 'mytable'
my_id_column = 'id'
my_other_columns = ['field1', 'field2']
my_values_hash = {
'id': 123,
'field1': "john",
'field2': "doe"
}
print upsert(my_table_name, my_id_column, my_other_columns, my_values_hash)
解决方案很简单,但不是立即解决。如果要使用此指令,则必须对 db 进行一次更改:
ALTER USER user SET search_path to 'name_of_schema';
在这些更改之后,“INSERT”将正常工作。
不定期副业成功案例分享
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') ON CONFLICT (did) DO NOTHING;
(2) 如果不存在则插入,否则更新 -INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
这些示例来自手册 - postgresql.org/docs/9.5/static/sql-insert.htmlON CONFLICT DO NOTHING RETURNING id
,请阅读此答案stackoverflow.com/a/42217872/368691。