Postgres: INSERT wenn nicht bereits vorhanden

Question

Ich verwende Python, um in eine Postgres-Datenbank zu schreiben:

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

Da aber einige meiner Zeilen identisch sind, erhalte ich folgende Fehlermeldung:

psycopg2.IntegrityError: duplicate key value  
  violates unique constraint "hundred_pkey"

Wie kann ich eine SQL-Anweisung "INSERT unless this row already exists" schreiben?

Ich habe gesehen, dass komplexe Aussagen wie diese empfohlen werden:

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

Aber erstens ist dies overkill für was ich brauche, und zweitens, wie kann ich eine von denen als eine einfache Zeichenfolge ausführen?

Answer 1

Hier ist eine generische Python-Funktion, die aus einem Tabellennamen, Spalten und Werten das Upsert-Äquivalent für Postgresql erzeugt.

json importieren

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)

Answer 2

Ich habe nach einer ähnlichen Lösung gesucht und versucht, SQL zu finden, das sowohl in PostgreSQL als auch in HSQLDB funktioniert. (HSQLDB war, was dies schwierig gemacht.) Mit Ihrem Beispiel als Grundlage, ist dies das Format, das ich anderswo gefunden.

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 );"

Answer 3

Die Lösung ist einfach, aber nicht sofort.
Wenn Sie diese Anweisung verwenden wollen, müssen Sie eine Änderung an der db vornehmen:

ALTER USER user SET search_path to 'name_of_schema';

nach diesen Änderungen wird "INSERT" korrekt funktionieren.