我使用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和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 );"

其他回答

我知道这个问题是很久以前提出的,但我想这可能会对一些人有所帮助。我认为最简单的方法就是触发。例如:

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

你可以在Postgres中使用VALUES:

INSERT INTO person (name)
    SELECT name FROM person
    UNION 
    VALUES ('Bob')
    EXCEPT
    SELECT name FROM person;

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

我建议您插入一个id作为串行类型来处理主键

不幸的是,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之间遇到竞争条件,其中另一个进程可能会在此期间创建该记录。