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

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

当前回答

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

INSERT ... ON CONFLICT DO NOTHING/UPDATE

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

2015-07-31 09:33:23

其他回答

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

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

2015-03-26 12:56:12

在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

2017-10-14 04:26:56

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

INSERT ... ON CONFLICT DO NOTHING/UPDATE

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

2015-07-31 09:33:23

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

2016-07-25 23:44:37

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

2014-09-26 06:53:00

Postgres:如果不存在则插入

推荐文章

最新文章

标签