Postgres: WSTAW, jeśli jeszcze nie istnieje

Używam Pythona do pisania w bazie danych Postgres:

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

Ale ponieważ niektóre z moich wierszy są identyczne, pojawia się następujący błąd:

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

Jak napisać instrukcję SQL „INSERT, chyba że ten wiersz już istnieje”?

Widziałem złożone takie polecenia, jak to zalecane:

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

Ale po pierwsze, czy to przesada w stosunku do tego, czego potrzebuję, a po drugie, jak mogę wykonać jedną z nich jako prosty ciąg?

Postgres 9.5 (wydany od 01.01.2016) oferuje polecenie „wstawiania” , znane również jako klauzula ON CONFLICT dla INSERT :

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Rozwiązuje wiele subtelnych problemów, na które możesz natknąć się podczas jednoczesnego działania, które proponują inne odpowiedzi.

Jak napisać instrukcję SQL „INSERT, chyba że ten wiersz już istnieje”?

Jest ładny sposób na wykonanie warunkowego INSERT w PostgreSQL:

INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );

CAVEAT Podejście to nie jest w 100% niezawodne dla równoczesnych operacji zapisu. Istnieje bardzo małe wyścigu pomiędzy SELECTw NOT EXISTSanty-semi-join a INSERTsama. To może nie w takich warunkach.

Jednym z podejść byłoby utworzenie tabeli nieograniczonej (bez unikalnych indeksów) do wstawienia wszystkich danych i wybranie innej opcji niż wstawienie do setki tabel.

Tak wysoki byłby poziom. Zakładam, że wszystkie trzy kolumny są różne w moim przykładzie, więc dla kroku 3 zmień połączenie NOT EXITS, aby połączyć tylko na unikalnych kolumnach w tabeli setek.

1. Utwórz tymczasowy stół. Zobacz dokumenty tutaj .

   CREATE TEMPORARY TABLE temp_data(name, name_slug, status);

2. Wstaw dane do tabeli temp.

   INSERT INTO temp_data(name, name_slug, status); 

3. Dodaj dowolne indeksy do tabeli temp.

4. Wykonaj wkładkę do stołu głównego.

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

Niestety PostgreSQLnie obsługuje MERGEani ON DUPLICATE KEY UPDATE, więc musisz to zrobić w dwóch instrukcjach:

UPDATE  invoices
SET     billed = 'TRUE'
WHERE   invoices = '12345'

INSERT
INTO    invoices (invoiceid, billed)
SELECT  '12345', 'TRUE'
WHERE   '12345' NOT IN
        (
        SELECT  invoiceid
        FROM    invoices
        )

Możesz zawinąć w funkcję:

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';

i po prostu nazwij to:

SELECT  fn_upd_invoices('12345', 'TRUE')

Możesz skorzystać z WARTOŚCI - dostępnych w Postgres:

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

Wiem, że to pytanie jest dawno temu, ale pomyślałem, że to może komuś pomóc. Myślę, że najłatwiej to zrobić za pomocą wyzwalacza. Na przykład:

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

Wykonaj ten kod z wiersza polecenia psql (lub jakkolwiek chcesz wykonywać zapytania bezpośrednio w bazie danych). Następnie możesz wstawić jak zwykle z Pythona. Na przykład:

sql = "Insert Into hundreds (name, name_slug, status) Values (%s, %s, %s)"
cursor.execute(sql, (hundred, hundred_slug, status))

Zauważ, że jak już wspomniano @Thomas_Wouters, powyższy kod wykorzystuje parametry zamiast konkatenacji łańcucha.

Jest ładny sposób na wykonanie warunkowego INSERT w PostgreSQL przy użyciu zapytania WITH: Like:

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 

Właśnie z tym mam problem, a moja wersja to 9.5

I rozwiązuję to za pomocą zapytania SQL poniżej.

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;

Mam nadzieję, że pomoże to komuś, kto ma ten sam problem z wersją> = 9.5.

Dziękuje za przeczytanie.

WSTAW .. GDZIE NIE ISTNIEJE to dobre podejście. A warunków wyścigu można uniknąć dzięki „kopercie” transakcji:

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

To proste dzięki regułom:

CREATE RULE file_insert_defer AS ON INSERT TO file
WHERE (EXISTS ( SELECT * FROM file WHERE file.id = new.id)) DO INSTEAD NOTHING

Ale to nie powiedzie się przy równoczesnym zapisie ...

Podejście z najbardziej pozytywnymi opiniami (od Johna Doe) w jakiś sposób działa dla mnie, ale w moim przypadku z oczekiwanych 422 wierszy dostaję tylko 180. Nie mogłem znaleźć nic złego i nie ma żadnych błędów, więc szukałem innego proste podejście.

Używanie IF NOT FOUND THENpo SELECTprostu działa idealnie dla mnie.

(opisane w dokumentacji PostgreSQL )

Przykład z dokumentacji:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', myname;
END IF;

Klasa kursora psycopgs ma atrybut rowcount .

Ten atrybut tylko do odczytu określa liczbę wierszy, które ostatnia funkcja * () utworzyła (dla instrukcji DQL, takich jak SELECT) lub wpłynęła na nią (dla instrukcji DML, takich jak UPDATE lub INSERT).

Możesz więc najpierw spróbować UPDATE i WSTAWIĆ tylko wtedy, gdy liczba wierszy wynosi 0.

Ale w zależności od poziomów aktywności w bazie danych możesz osiągnąć warunek wyścigu między UPDATE a INSERT, w którym inny proces może utworzyć ten rekord w międzyczasie.

Twoja kolumna „sto” wydaje się być zdefiniowana jako klucz podstawowy i dlatego musi być unikalna, co nie jest prawdą. Problem nie jest związany z twoimi danymi.

Sugeruję, aby wstawić identyfikator jako typ szeregowy, aby podać klucz podstawowy

Jeśli powiesz, że wiele wierszy jest identycznych, skończysz sprawdzanie wiele razy. Możesz je wysłać, a baza danych określi, czy wstawić, czy nie, z klauzulą ​​ON CONFLICT w następujący sposób

  INSERT INTO Hundred (name,name_slug,status) VALUES ("sql_string += hundred  
  +",'" + hundred_slug + "', " + status + ") ON CONFLICT ON CONSTRAINT
  hundred_pkey DO NOTHING;" cursor.execute(sql_string);

Szukałem podobnego rozwiązania, próbując znaleźć SQL, który działa zarówno w PostgreSQL, jak i HSQLDB. (To właśnie utrudniało HSQLDB.) Korzystając z twojego przykładu jako podstawy, jest to format, który znalazłem gdzie indziej.

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

Oto ogólna funkcja python, która podając tablename, kolumny i wartości, generuje odpowiednik upsert dla postgresql.

import 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)

Rozwiązanie jest proste, ale nie natychmiastowe.
Jeśli chcesz skorzystać z tej instrukcji, musisz wprowadzić jedną zmianę w db:

ALTER USER user SET search_path to 'name_of_schema';

po tych zmianach „WSTAW” będzie działać poprawnie.