[postgresql] Postgres : 존재하지 않는 경우 INSERT

Program / 글쓴이

파이썬을 사용하여 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"

‘이 행이 존재하지 않는 한’INSERT ‘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 이후 출시)는 INSERT 의 ON CONFLICT 절 이라고도 하는 “upsert” 명령을 제공합니다 .

INSERT ... ON CONFLICT DO NOTHING/UPDATE

그것은 동시 작업을 사용할 때 발생할 수있는 많은 미묘한 문제를 해결합니다.

답변

‘이 행이 존재하지 않는 한’INSERT ‘SQL 문을 작성하려면 어떻게해야합니까?

PostgreSQL에서 조건부 INSERT를 수행하는 좋은 방법이 있습니다.

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

주의 사항 이 방법은 동시 쓰기 작업에 100 % 신뢰할 수 없습니다 . 사이 아주 작은 경쟁 조건이 SELECT의 NOT EXISTS반 반이-가입하고 INSERT자체. 그것은 수 와 같은 조건에서 실패합니다.

답변

한 가지 방법은 제한되지 않은 (고유 인덱스가없는) 테이블을 만들어 모든 데이터를 삽입하고 그와 다른 선택을 수행하여 백 테이블에 삽입하는 것입니다.

따라서 높은 수준입니다. 필자의 예제에서는 세 열이 모두 고유하다고 가정하므로 step3에서는 NOT EXITS 조인을 백 테이블의 고유 열에서만 조인하도록 변경하십시오.

  1. 임시 테이블을 만듭니다. 여기에서 문서를 참조 하십시오 .

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

  2. 임시 테이블에 데이터 삽입

    INSERT INTO temp_data(name, name_slug, status);

  3. 임시 테이블에 인덱스를 추가하십시오.

  4. 메인 테이블 인서트를 수행하십시오.

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

답변

불행히도 nor도 PostgreSQL지원하지 않으므로 두 가지 진술로해야합니다.MERGEON 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')

답변

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가 이미 언급했듯이 위의 코드는 문자열을 연결하는 대신 매개 변수를 활용합니다.

답변

WITH 쿼리를 사용하여 PostgreSQL에서 조건부 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