我使用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
但首先，这对我需要的东西来说是不是太过了，其次，我怎么能把它们作为一个简单的字符串来执行呢?
不幸的是，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')
我正在寻找一个类似的解决方案，试图找到在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 );"
一种方法是创建一个不受约束(没有唯一索引)的表，将所有数据插入其中，并执行与插入到百位表不同的选择。
所以高水平。我假设在我的示例中所有三个列都是不同的，因此对于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
);
