Postgres:如果不存在则插入

我使用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

但首先，这对我需要的东西来说是不是太过了，其次，我怎么能把它们作为一个简单的字符串来执行呢?

541820 次浏览
小开

不幸的是，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')
小开

psycopgs游标类具有rowcount属性。

这个只读属性指定了最后的行数 execute*()产生的(对于像SELECT这样的DQL语句)或影响的(对于 DML语句，如UPDATE或INSERT).

因此，您可以先尝试UPDATE，然后仅在rowcount为0时尝试INSERT。

但是根据数据库中的活动级别，您可能会在UPDATE和INSERT之间遇到竞争条件，其中另一个进程可能会在此期间创建该记录。

小开

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

所以高水平。我假设在我的示例中所有三个列都是不同的，因此对于step3，将NOT EXITS连接更改为只连接100表中唯一的列。

  1. 创建临时表参见文档这里<强> < / >强。

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

  2. INSERT Data into temp table.

    INSERT INTO temp_data(name, name_slug, status);

  3. Add any indexes to the temp table.

  4. 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
);
小开

你可以在Postgres中使用VALUES:

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已经提到的，上面的代码利用了参数，而不是连接字符串。

小开

我怎么能写一个'插入，除非这行已经存在' SQL语句?

在PostgreSQL中有一个很好的方法来执行有条件的INSERT:

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

但是，对于EYZ4写操作，这种方法并不是100%可靠。在NOT EXISTS反半连接中的SELECT和INSERT本身之间有一个非常小的竞争条件。在这样的条件下，它不可能失败。

小开
获得最多赞的方法(来自John Doe)在某种程度上对我有用，但在我的情况下，从预期的422行中，我只得到180行。 我找不到任何错误，根本没有错误，所以我寻找一个不同的简单的方法

在SELECT之后使用IF NOT FOUND THEN非常适合我。

(在PostgreSQL的文档中描述)

来自文档的例子:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
小开

我正在寻找一个类似的解决方案，试图找到在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 );"
小开

插入……“不存在的地方”是个好方法。而竞争条件可以通过事务“信封”来避免:

BEGIN;
LOCK TABLE hundred IN SHARE ROW EXCLUSIVE MODE;
INSERT ... ;
COMMIT;
小开

Postgres 9.5(发布于2016-01-07)提供了“插入”命令，也称为插入的ON冲突子句:

INSERT ... ON CONFLICT DO NOTHING/UPDATE

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

小开

有规则很简单:

CREATE RULE file_insert_defer AS ON INSERT TO file
WHERE (EXISTS ( SELECT * FROM file WHERE file.id = new.id)) DO INSTEAD NOTHING

但是并发写失败了…

小开

这是一个通用的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';

在这些改变后，“插入”将正常工作。

小开
在PostgreSQL中使用WITH查询有一个很好的方法来做条件插入: 如:< / p > 
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
小开

您的列“hundred”似乎被定义为主键，因此必须是唯一的，但事实并非如此。问题不在于你，而在于你的数据。

我建议您插入一个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);
小开

这正是我所面临的问题，我的版本是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版本有同样问题的人。

感谢阅读。

小开

我们可以使用upsert简化查询

insert into invoices (invoiceid, billed)
values ('12345', 'TRUE')
on conflict (invoiceid) do
update set billed=EXCLUDED.billed;
小开 
INSERT INTO invoices (invoiceid, billed) (
SELECT '12345','TRUE' WHERE NOT EXISTS (
SELECT 1 FROM invoices WHERE invoiceid='12345' AND billed='TRUE'
)
)

提交答案