我们可以使用upsert简化查询

insert into invoices (invoiceid, billed) 
  values ('12345', 'TRUE') 
  on conflict (invoiceid) do 
    update set billed=EXCLUDED.billed;

不幸的是，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中使用WITH查询有一个很好的方法来执行有条件的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 

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

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

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

感谢阅读。

INSERT INTO invoices (invoiceid, billed) (
    SELECT '12345','TRUE' WHERE NOT EXISTS (
        SELECT 1 FROM invoices WHERE invoiceid='12345' AND billed='TRUE'
        )
)