programing

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

padding 2023. 5. 23. 21:36

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

서 매우 인데, 에서 MySQL이라고 부르는 입니다.INSERT ... ON DUPLICATE UPDATE은 그고표지일서부의 로 지원합니다.MERGE작동.

포스트그레를 생각하면,SQL은 직접 지원하지 않습니다(페이지 9.5 이전). 어떻게 해야 합니까?다음 사항을 고려합니다.

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

튜플 이제위 "튜으로을 "쪽고싶 " 다고상 " 리해상보 " 세요올 " 플" ▁the해보 "을 로 올려놓기를 .(2, 'Joe'),(3, 'Alan')따라서 새 테이블 내용은 다음과 같습니다.

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

그것이 사람들이 토론할 때 이야기하는 것입니다.upsert결정적으로, 명시적 잠금을 사용하거나 결과적인 경쟁 조건을 방어하여 동일한 테이블에서 여러 트랜잭션이 작동하는 경우 모든 접근 방식이 안전해야 합니다.

이 주제는 PostgreSQL의 중복 업데이트에 대한 Insert에서 광범위하게 논의되지만, MySQL 구문의 대안에 대한 것이며, 시간이 지남에 따라 관련 없는 세부 사항이 상당히 증가했습니다.저는 결정적인 답을 찾고 있습니다.

이러한 기술은 "존재하지 않는 경우 삽입, 그렇지 않으면 아무것도 하지 않음", 즉 "삽입..."에도 유용합니다.중복 키 무시 시".

9.5 이상:

9은 Postgre를 지원합니다.SQL 9.5 이상 지원INSERT ... ON CONFLICT (key) DO UPDATE)ON CONFLICT (key) DO NOTHING), .), 즉, upvert.

과의 비교.

빠른 설명.

사용법은 설명서, 특히 구문 다이어그램의 conflict_action 절과 설명 텍스트를 참조하십시오.

아래에 제공된 9.4 이상 버전의 솔루션과 달리 이 기능은 여러 행이 충돌할 때 작동하며 전용 잠금이나 재시도 루프가 필요하지 않습니다.

기능을 추가하는 커밋은 여기에 있으며 개발에 대한 논의는 여기에 있습니다.

9.5 버전이고 이전 버전과 호환될 필요가 없다면 이제 읽기를 중지할 수 있습니다.

9.4 이상:

제공 Postgre가 SQL에는 기본 제공 기능이 없습니다.UPSERT(또는)MERGE 동시 하는 것은 의 시설과 동시에 사용하는 상황에서 효율적으로 수행하는 것은 매우 어렵습니다.

이 기사는 그 문제를 유용하게 자세히 설명합니다.

일반적으로 다음 두 가지 옵션 중 하나를 선택해야 합니다.

재시도 루프의 개별 삽입/업데이트 작업 또는
테이블 잠금 및 배치 병합 수행

개별 행 재시도 루프

여러 연결이 동시에 삽입을 수행하려는 경우 재시도 루프에서 개별 행 업서트를 사용하는 것이 합리적인 옵션입니다.

더 포스트그SQL 설명서에는 데이터베이스 내부의 루프에서 이 작업을 수행할 수 있는 유용한 절차가 포함되어 있습니다.대부분의 단순한 솔루션과 달리 업데이트 및 삽입 레이스가 손실되지 않도록 보호합니다.다음에서만 작동합니다.READ COMMITTED모드이며 트랜잭션에서 유일하게 수행하는 작업인 경우에만 안전합니다.트리거 또는 보조 고유 키로 인해 고유한 위반이 발생하면 이 기능이 제대로 작동하지 않습니다.

이 전략은 매우 비효율적입니다.실용적일 때마다 작업을 대기열에 올리고 대신 아래 설명된 대로 대량 업셋을 수행해야 합니다.

이 문제에 대한 많은 시도된 솔루션이 롤백을 고려하지 못하므로 불완전한 업데이트가 발생합니다.하는데, 그중가 성공적으로 이루어졌습니다.INSERT키하여 s를 합니다; 다른하나중키수실신행다니합고하오를류복.UPDATE대신.그UPDATE블록이 대기 중입니다.INSERT롤백하거나 커밋합니다. 그이뒤굴면가러로것,면▁when▁the,UPDATE하므로 조재행 , UPDATE당신이 기대했던 것보다 더 흥분하지 않았다고 커밋합니다.결과 행 수를 확인하고 필요한 경우 다시 시도해야 합니다.

시도된 일부 솔루션도 SELECT 레이스를 고려하지 못합니다.분명하고 간단한 방법을 시도해 보면 다음과 같습니다.

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

두 개가 동시에 실행되면 여러 고장 모드가 있습니다.하나는 업데이트 재점검과 관련하여 이미 논의된 문제입니다.다른 하나는 두 가지 모두가UPDATE동시에 0개의 행을 일치시키고 계속합니다.그리고 그들은 둘 다 합니다.EXISTS테스트, 그것은 이전에 발생합니다.INSERT 다 행이 에 둘 다 이 없어요.INSERT중복 키 오류로 인해 하나가 실패합니다.

이것이 당신이 재시도 루프가 필요한 이유입니다.지능형 SQL을 사용하면 중복 키 오류나 업데이트 손실을 방지할 수 있다고 생각할 수 있지만, 그렇지 않습니다.행 수를 확인하거나 중복 키 오류를 처리한 후(선택한 접근 방식에 따라) 다시 시도해야 합니다.

이 문제에 대해 독자적인 해결책을 제시하지 마십시오.메시지 큐와 마찬가지로 잘못된 것일 수 있습니다.

잠금 기능이 있는 대량 업셋

이전의 기존 데이터 세트로 병합할 새 데이터 세트가 있는 대량 업버트를 수행할 수도 있습니다.이는 개별 행 업셋보다 훨씬 효율적이며 실용적일 때마다 선호해야 합니다.

이 경우 일반적으로 다음 프로세스를 수행합니다.

CREATE a TEMPORARY테이블
COPY또는 새 데이터를 임시 테이블에 대량으로 삽입합니다.
LOCK목표표IN EXCLUSIVE MODE이렇게 하면 다른 트랜잭션이 허용됩니다.SELECT테이블을 변경하지 않습니다.
를 .UPDATE ... FROM온도 표의 값을 사용하는 기존 기록의 비율
를 .INSERT대상 테이블에 아직 존재하지 않는 행의 수;
COMMIT잠금을 해제합니다.

를 들어, 사용하는 , 다치를 사용하는 경우, 다치를 사용하는 입니다.INSERT온도 테이블 채우기

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer, somedata text);

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;

때어는?`MERGE`?

표준 SQL 문서MERGE실제로 정의된 동시성 의미가 잘못되어 테이블을 먼저 잠그지 않고 반전하기에는 적합하지 않습니다.

이것은 데이터 병합에 매우 유용한 OLAP 문이지만 동시성-안전성 향상을 위한 유용한 솔루션은 아닙니다.에게 DBMS를 MERGE하지만 사실은 틀렸습니다.

기타 DB:

INSERT ... ON DUPLICATE KEY UPDATE MySQL에서
MERGE MS SQL Server에서 사용할 수 있습니다(단, 위 정보 참조).MERGE문제)
MERGE Oracle에서 제공(단, 위의 정보 참조)MERGE문제)

▁examples다니▁here예입에 대한 몇 가지 예가 있습니다.insert ... on conflict ...(pg 9.5+):

삽입, 충돌 시 - 아무 것도 하지 않습니다.

insert into dummy(id, name, size) values(1, 'new_name', 3)
on conflict do nothing;`

충돌 - 업데이트할 때 열을 통해 충돌 대상을 지정합니다.

insert into dummy(id, name, size) values(1, 'new_name', 3)
on conflict(id)
do update set name = 'new_name', size = 3;

충돌 - 업데이트 시 제약 조건 이름을 통해 충돌 대상을 지정합니다.

insert into dummy(id, name, size) values(1, 'new_name', 3)
on conflict on constraint dummy_pkey
do update set name = 'new_name', size = 4;

Postgre 9.5 이전 버전의 단일 삽입 문제에 대한 다른 솔루션을 제공하려고 합니다.SQL. 이 개념은 단순히 삽입을 먼저 수행하고 레코드가 이미 있는 경우 업데이트를 시도하는 것입니다.

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

이 솔루션은 테이블 행이 삭제되지 않은 경우에만 적용할 수 있습니다.

이 솔루션의 효율성에 대해서는 잘 모르겠지만, 충분히 합리적인 것 같습니다.

Postgres에 대한 SQLLchemy upsert >=9.5

위의 큰 게시물은 Postgres 버전(질문에서와 같이 9.5가 아닌 버전)에 대한 다양한 SQL 접근 방식을 포함하므로 Postgres 9.5를 사용하는 경우 SQLLchemy에서 수행하는 방법을 추가하고자 합니다.사용자 고유의 업셋을 구현하는 대신 SQLAlchemy 1.1에 추가된 SQLAlchemy의 기능을 사용할 수도 있습니다.개인적으로, 가능하다면 이것들을 사용하는 것을 추천합니다.편리함 뿐만 아니라 Postgre를 사용할 수 있게 해주기 때문입니다.SQL은 발생할 수 있는 모든 경합 조건을 처리합니다.

어제 드린 다른 답변의 크로스 포스트(https://stackoverflow.com/a/44395983/2156909)

는 SQL을 지원합니다.ON CONFLICT 두 on_conflict_do_update()그리고.on_conflict_do_nothing():

설명서에서 복사:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

Postgre에서 병합SQL v. >=15

포스트그레 이후SQL v.15는 명령어를 사용할 수 있습니다.실제로 이 새로운 버전의 주요 개선 사항 중 하나로 제시되었습니다.

를 사용합니다.WHEN MATCHED/WHEN NOT MATCHED동일한 기준을 가진 기존 행이 있을 때 동작을 선택하기 위한 조건부.

표준 ▁standard다▁▁than보다 더 좋습니다.UPSERT새로운 기능이 완전히 제어할 수 있게 됨에 따라INSERT,UPDATE또는DELETE한 줄로 늘어선 행

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value)

WITH UPD AS (UPDATE TEST_TABLE SET SOME_DATA = 'Joe' WHERE ID = 2 
RETURNING ID),
INS AS (SELECT '2', 'Joe' WHERE NOT EXISTS (SELECT * FROM UPD))
INSERT INTO TEST_TABLE(ID, SOME_DATA) SELECT * FROM INS

Postgresql 9.3에서 테스트됨

이 질문이 종결되었기 때문에, 저는 SQL 화학을 사용하는 방법에 대해 여기에 글을 합니다.재귀를 통해 대량 삽입 또는 업데이트를 재시도하여 레이스 상태 및 유효성 검사 오류를 방지합니다.

우선 수입품들.

import itertools as it

from functools import partial
from operator import itemgetter

from sqlalchemy.exc import IntegrityError
from app import session
from models import Posts

이제 몇 가지 도우미 기능이 있습니다.

def chunk(content, chunksize=None):
    """Groups data into chunks each with (at most) `chunksize` items.
    https://stackoverflow.com/a/22919323/408556
    """
    if chunksize:
        i = iter(content)
        generator = (list(it.islice(i, chunksize)) for _ in it.count())
    else:
        generator = iter([content])

    return it.takewhile(bool, generator)


def gen_resources(records):
    """Yields a dictionary if the record's id already exists, a row object 
    otherwise.
    """
    ids = {item[0] for item in session.query(Posts.id)}

    for record in records:
        is_row = hasattr(record, 'to_dict')

        if is_row and record.id in ids:
            # It's a row but the id already exists, so we need to convert it 
            # to a dict that updates the existing record. Since it is duplicate,
            # also yield True
            yield record.to_dict(), True
        elif is_row:
            # It's a row and the id doesn't exist, so no conversion needed. 
            # Since it's not a duplicate, also yield False
            yield record, False
        elif record['id'] in ids:
            # It's a dict and the id already exists, so no conversion needed. 
            # Since it is duplicate, also yield True
            yield record, True
        else:
            # It's a dict and the id doesn't exist, so we need to convert it. 
            # Since it's not a duplicate, also yield False
            yield Posts(**record), False

그리고 마지막으로 업서스트 기능은

def upsert(data, chunksize=None):
    for records in chunk(data, chunksize):
        resources = gen_resources(records)
        sorted_resources = sorted(resources, key=itemgetter(1))

        for dupe, group in it.groupby(sorted_resources, itemgetter(1)):
            items = [g[0] for g in group]

            if dupe:
                _upsert = partial(session.bulk_update_mappings, Posts)
            else:
                _upsert = session.add_all

            try:
                _upsert(items)
                session.commit()
            except IntegrityError:
                # A record was added or deleted after we checked, so retry
                # 
                # modify accordingly by adding additional exceptions, e.g.,
                # except (IntegrityError, ValidationError, ValueError)
                db.session.rollback()
                upsert(items)
            except Exception as e:
                # Some other error occurred so reduce chunksize to isolate the 
                # offending row(s)
                db.session.rollback()
                num_items = len(items)

                if num_items > 1:
                    upsert(items, num_items // 2)
                else:
                    print('Error adding record {}'.format(items[0]))

사용 방법은 다음과 같습니다.

>>> data = [
...     {'id': 1, 'text': 'updated post1'}, 
...     {'id': 5, 'text': 'updated post5'}, 
...     {'id': 1000, 'text': 'new post1000'}]
... 
>>> upsert(data)

이것은 대량 작업과 달리 삽입 시 관계, 오류 확인 등을 처리할 수 있다는 장점이 있습니다.

언급URL : https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql

'programing' 카테고리의 다른 글

VB.NET에서 Enter 키를 누르는 중 (0)	2023.05.23
WPF를 사용하여 레이블에서 텍스트를 랩핑하려면 어떻게 해야 합니까? (0)	2023.05.23
NameError: 'reload' 이름이 정의되지 않았습니다. (0)	2023.05.18
기본 컬렉션 및 데이터로 Mongo Docker 이미지를 만드는 방법은 무엇입니까? (0)	2023.05.18
WPF 생성비트맵 원본HBitmap() 메모리 누수 (0)	2023.05.18

현재글UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

각종 프로그래밍 정보를 다루는 블로그입니다.

sql-server, powershell, Wordpress, spring-boot, AJAX, git, MongoDB, asp.net, Excel, MariaDB, iphone, oracle, MySQL, jQuery, Android, Python, C, typeScript, Bash, AngularJS,

Today :
Yesterday :

padding

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

9.5 이상:

9.4 이상:

개별 행 재시도 루프

잠금 기능이 있는 대량 업셋

관련 판독치

때어는?`MERGE`?

기타 DB:

Postgres에 대한 SQLLchemy upsert >=9.5

Postgre에서 병합SQL v. >=15

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2024/09 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

UPSERT 방법(MERGE, INSERT...)PostgreSQL에서 중복 업데이트 시)?

9.5 이상:

9.4 이상:

개별 행 재시도 루프

잠금 기능이 있는 대량 업셋

관련 판독치

때어는?MERGE?

기타 DB:

Postgres에 대한 SQLLchemy upsert >=9.5

Postgre에서 병합SQL v. >=15

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바

때어는?`MERGE`?