source

Postgres: INSERT(이미 존재하지 않는 경우)

manysource 2023. 5. 5. 09:54

Postgres: INSERT(이미 존재하지 않는 경우)

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"

'이 행이 이미 존재하지 않는 한 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 CONCLIVE 절이라고도 하는 "upsert" 명령을 제공합니다.

INSERT ... ON CONFLICT DO NOTHING/UPDATE

동시 작업을 사용할 때 발생할 수 있는 여러 가지 미묘한 문제를 해결하며, 이는 일부 다른 답변에서 제안합니다.

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

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

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

주의: 그러나 이 접근 방식은 동시 쓰기 작업에 대해 100% 신뢰할 수 없습니다.그들 사이에는 아주 작은 경주 조건이 있습니다.SELECT에 시대에NOT EXISTS▁anti▁the.INSERT그 자체로이러한 조건에서는 실패할 수 있습니다.

한 가지 접근 방식은 모든 데이터를 삽입할 비제한(고유 인덱스 없음) 테이블을 만들고 100개의 테이블에 삽입할 때와는 다른 선택을 하는 것입니다.

높은 수준일 것입니다.이 예에서는 세 개의 열이 모두 구별되므로 3단계에서는 NOT EXECUES 조인을 100개 테이블의 고유한 열에만 조인하도록 변경합니다.

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

이것이 바로 제가 직면한 문제이고 제 버전은 9.5입니다.

그리고 아래 SQL 쿼리로 해결합니다.

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;

그것이 버전 >= 9.5와 같은 문제를 가진 사람에게 도움이 되기를 바랍니다.

읽어주셔서 감사합니다.

불하게도행,,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')

Postgres에서 사용할 수 있는 VALUE를 사용할 수 있습니다.

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_는위의 코드는 문자열을 연결하는 대신 매개 변수를 사용합니다.

Postgre에서 조건부 INSERT를 수행하는 좋은 방법이 있습니다.WITH 쿼리를 사용한 SQL: 예:

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

업서트를 사용하여 쿼리를 단순화할 수 있습니다.

insert into invoices (invoiceid, billed) 
  values ('12345', 'TRUE') 
  on conflict (invoiceid) do 
    update set billed=EXCLUDED.billed;

삽입...존재하지 않는 곳이 좋은 방법입니다.그리고 인종 조건은 트랜잭션 "봉투"를 통해 피할 수 있습니다.

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

그러나 동시 쓰기로 인해 실패합니다...

(John Doe의) 가장 많은 표를 얻은 접근 방식은 어떻게든 저에게 효과가 있지만, 제 경우 예상되는 422개 행에서 180개만 얻을 수 있습니다.저는 잘못된 것을 찾을 수 없었고 오류도 전혀 없어서 다른 간단한 접근법을 찾았습니다.

용사를 합니다.IF NOT FOUND THEN▁a▁after시.SELECT저한테 딱 맞는 것 같아요.

(Postgre에 설명됨)SQL 설명서)

설명서의 예:

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

psycopgs 커서 클래스에 속성 행 수가 있습니다.

이 읽기 전용 특성은 마지막 실행*()이 생성한 행 수(SELECT와 같은 DQL 문의 경우) 또는 영향을 받은 행 수(UPDATE 또는 INSERT와 같은 DML 문의 경우)를 지정합니다.

따라서 행 수가 0인 경우에만 UPDATE를 먼저 시도하고 INSERT를 시도할 수 있습니다.

그러나 데이터베이스의 활동 수준에 따라 UPDATE와 INSERT 사이에 경합 조건이 발생할 수 있으며, 중간에 다른 프로세스가 해당 레코드를 작성할 수 있습니다.

열 "백"이 기본 키로 정의된 것 같습니다. 따라서 고유해야 합니다. 이는 해당되지 않습니다.문제는 데이터가 아니라 데이터에 있습니다.

기본 키를 처리하려면 일련 유형으로 ID를 삽입하는 것이 좋습니다.

많은 행이 동일하다고 말하면 여러 번 검사를 종료합니다.전송할 수 있으며 데이터베이스는 다음과 같이 ON CONCLIVE 절로 삽입 여부를 결정합니다.

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

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

Postgre에서 작동하는 SQL을 찾으려고 비슷한 솔루션을 찾고 있었습니다.SQL 및 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 );"

다음은 테이블 이름, 열 및 값이 주어지면 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)

간단한 해결책이지만 즉시 해결할 수는 없습니다.
이 지침을 사용하려면 DB를 변경해야 합니다.

ALTER USER user SET search_path to 'name_of_schema';

변경한 후에는 "INSERT"가 올바르게 작동합니다.

언급URL : https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already

'source' 카테고리의 다른 글

Azure/에서 과도한 HTTP 응답 헤더 제거/숨기기/사용 안 함UrlScan이 없는 IIS7  (0) 2023.05.05
MongoDB vs.카산드라 vs.실시간 광고 플랫폼용 MySQL  (0) 2023.05.05
Visual Studio에서 WPF Initialize Component 메서드를 찾을 수 없는 이유는 무엇입니까?  (0) 2023.05.05
"작업이 트랜잭션 상태에 대해 유효하지 않습니다." 오류 및 트랜잭션 범위  (0) 2023.05.05
후드 아래에서 잠금 문은 무엇을 합니까?  (0) 2023.05.05

'source'의 다른글

  • 현재글Postgres: INSERT(이미 존재하지 않는 경우)

관련글

댓글

티스토리툴바