Postgres: INSERT, если еще не существует

Как я могу написать "INSERT, если эта строка уже не существует" SQL-выражение?

Есть хороший способ сделать условный INSERT в PostgreSQL:

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

CAVEAT. Этот подход не является на 100% надежным для одновременных операций записи. Между SELECT существует очень крошечное состояние гонки в анти-полусоединении NOT EXISTS и самом INSERT. В таких условиях он может выйти из строя.

Postgres 9.5 (выпущен с 2016-01-07) предлагает команду "upsert" , также известную как Включение CONFLICT в INSERT:

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Он решает многие из тонких проблем, с которыми вы можете столкнуться при использовании параллельной операции, которую предлагают некоторые другие ответы.

Одним из подходов было бы создание таблицы без ограничений (без уникальных индексов) для вставки всех ваших данных и выбора, отличного от того, чтобы вставить вашу стоп-таблицу в сто таблицу.

Такой высокий уровень. Я предполагаю, что все три столбца отличаются в моем примере, поэтому для шага 3 измените соединение NOT EXITS, чтобы присоединиться только к уникальным столбцам в сто таблице.

• Создайте временную таблицу. См. Docs здесь.

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

• INSERT Данные в таблицу temp.

  INSERT INTO temp_data(name, name_slug, status); 
  

• Добавьте любые индексы в таблицу temp.

• Введите основную таблицу.

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

К сожалению, 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')

Если вы просто хотите вставить или не вставить (и не обновлять в противном случае), вы можете сделать это так (используя пример счета):

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

Вы можете использовать VALUES - доступно в Postgres:

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

Я знаю, что этот вопрос был давным-давно, но думал, что это может кому-то помочь. Я думаю, что самый простой способ сделать это - с помощью триггера. Например:.

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, в приведенном выше коде используются параметры, а не конкатенация строки.

INSERT.. WHERE NOT EXISTS - хороший подход. И условия гонки можно избежать транзакционным "конвертом":

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

Легко с правилами:

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

Но он терпит неудачу при одновременной записи...

Есть хороший способ сделать условный INSERT в PostgreSQL с помощью запроса WITH: Как:

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 

Подход с большинством upvotes (от John Doe) как-то работает для меня, но в моем случае из ожидаемых 422 строк я получаю только 180. Я не мог найти ничего плохого и ошибок не было, поэтому я искал другой простой подход.

Использование IF NOT FOUND THEN после SELECT просто отлично работает для меня.

(описано в Документация PostgreSQL)

Пример из документации:

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

Класс курсора psycopgs имеет атрибут rowcount.

Этот атрибут только для чтения указывает количество строк, которое последний execute *() (для операторов DQL, таких как SELECT) или затронутых (для DML, такие как UPDATE или INSERT).

Итак, вы можете сначала попробовать UPDATE и INSERT, только если rowcount равен 0.

Но в зависимости от уровней активности в вашей базе данных вы можете столкнуться с состоянием гонки между UPDATE и INSERT, где другой процесс может создать эту запись в промежуточный период.

Вот общая функция python, которая задает имя, столбцы и значения tablename, генерирует эквивалент upsert для 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)

Я искал аналогичное решение, пытаясь найти SQL, который работает в PostgreSQL, а также в HSQLDB. (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 );"

Решение в простой, но не сразу.
Если вы хотите использовать эту инструкцию, вы должны внести одно изменение в db:

ALTER USER user SET search_path to 'name_of_schema';

после этих изменений "INSERT" будет работать правильно.