programing

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

abcjava 2023. 5. 21. 10:45

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

몇 달 전에 Stack Overflow에 대한 답변에서 다음 구문을 사용하여 MySQL에서 여러 업데이트를 한 번에 수행하는 방법을 참조하십시오.

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

저는 이제 Postgre로 전환했습니다.SQL 그리고 분명히 이것은 정확하지 않습니다.그것은 모든 올바른 표를 참조하고 있기 때문에 다른 키워드가 사용되는 문제라고 생각하지만 Postgre의 어디에 있는지는 잘 모르겠습니다.여기에서는 SQL 설명서에 대해 설명합니다.

명확하게 하기 위해, 나는 몇 가지를 삽입하고 그것들이 이미 존재하는지 여부를 업데이트하고 싶습니다.

버전 9.5 이후의 PostgreSQL에는 ON CONCLIVEL 절이 있는 UPSERT 구문이 있습니다.다음 구문을 사용합니다(MySQL과 유사).

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

postgresql의 e-메일 그룹 아카이브에서 "upsert"를 검색하면 매뉴얼에서 원하는 작업을 수행하는 예를 찾을 수 있습니다.

예 38-2.UPDATE/INSERT에 대한 예외

이 예에서는 예외 처리를 사용하여 적절한 경우 UPDATE 또는 INSERT를 수행합니다.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

해커 메일링 목록에는 9.1 이상의 CTE를 사용하여 대량으로 이 작업을 수행하는 방법의 예가 있을 수 있습니다.

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

자세한 예는 _horse_with_no_name의 답변을 참조하십시오.

경고: 여러 세션에서 동시에 실행되는 경우에는 안전하지 않습니다(아래 주의 사항 참조).

postgresql에서 "UPSERT"를 수행하는 또 다른 현명한 방법은 각각 성공하거나 영향을 미치지 않도록 설계된 두 개의 순차적 UPDATE/INSERT 문을 수행하는 것입니다.

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

"id=3"인 행이 이미 있으면 업데이트가 성공하고, 그렇지 않으면 업데이트가 적용되지 않습니다.

INSERT는 "id=3" 행이 아직 존재하지 않는 경우에만 성공합니다.

이 두 가지를 단일 문자열로 결합하고 응용 프로그램에서 단일 SQL 문을 실행하여 두 가지를 모두 실행할 수 있습니다.단일 트랜잭션에서 함께 실행하는 것이 좋습니다.

이 기능은 독립적으로 실행되거나 잠긴 테이블에서 실행될 때 매우 잘 작동하지만, 경합 조건에 따라 행이 동시에 삽입된 경우에도 중복 키 오류로 인해 실패하거나 행이 동시에 삭제될 때 행이 삽입되지 않은 상태로 종료될 수 있습니다.SERIALIZABLEPostgre에서의 거래SQL 9.1 이상은 매우 높은 직렬화 실패율을 감수하면서도 안정적으로 처리되므로 많은 시도를 해야 합니다.이 사례에 대해 자세히 설명하는 upersert가 왜 그렇게 복잡한지 확인하십시오.

또한 이 접근 방식은 응용 프로그램이 영향을 받는 행 수를 확인하고 또는 영향을 받는 행 수를 확인하지 않는 한 업데이트가 손실될 수 있습니다.

Postgre 포함SQL 9.1 이는 쓰기 가능한 CTE(공통 테이블 표현식)를 사용하여 달성할 수 있습니다.

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

다음 블로그 항목 보기:

이 솔루션은 고유한 키 위반을 방지하지는 않지만 업데이트 손실에 취약하지는 않습니다.
dba.stackexchange.com 에서 Craig Ringer의 후속 작업을 확인하십시오.

Postgre 스트그서SQL 9 9.5를 할 수 .INSERT ... ON CONFLICT UPDATE.

설명서를 참조하십시오.

MySQLINSERT ... ON DUPLICATE KEY UPDATE다음으로 직접 대체할 수 있습니다.ON CONFLICT UPDATESQL 표준 구문도 아닙니다. 둘 다 데이터베이스별 확장입니다.여기에 사용되지 않은 좋은 이유가 있습니다. 새로운 구문은 단지 재미로 만들어진 것이 아닙니다. (MySQL의 구문에도 직접 채택되지 않았다는 것을 의미하는 문제가 있습니다.

예: 지정된 설정:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

MySQL 쿼리:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

다음이 됩니다.

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

차이점:

고유성 검사에 사용할 열 이름(또는 고유 제약 조건 이름)을 지정해야 합니다.그것이 바로ON CONFLICT (columnname) DO
키드워.SET이것이 정상인 것처럼 사용되어야 합니다.UPDATE

몇 가지 좋은 기능도 있습니다.

당신은 가질 수 있습니다.WHERE의 당의조에 UPDATE이 효과적으로 뒤집을 수 .)ON CONFLICT UPDATE안으로ON CONFLICT IGNORE특정 값의 경우)
을 위해 제안된 은 행 삽을 제된 값안 행은 할 수 .EXCLUDED대상 테이블과 동일한 구조를 가집니다.테이블 이름을 사용하여 테이블의 원래 값을 가져올 수 있습니다. 이 에는 ㅠㅠㅠㅠㅠㅠㅠㅠㅠEXCLUDED.c▁▁be 될 것입니다.10가 삽입하려고 (으)ㄹ 수 있습니다."table".c▁▁be 될 것입니다.3테이블의 현재 값이기 때문입니다.에서 둘 중 하나 또는 둘 다 사용할 수 있습니다.SET과 현표및WHERE절

upsert에 대한 배경은 UPSERT 방법(MERGE, INSERT...)을 참조하십시오. PostgreSQL에서 중복 업데이트 시)?

제가 여기 왔을 때도 같은 것을 찾고 있었는데, 일반적인 "업데이트" 기능이 없어서 조금 신경이 쓰여서 그냥 업데이트를 전달하고 매뉴얼에서 해당 기능의 인수로 SQL을 삽입할 수 있다고 생각했습니다.

다음과 같이 표시됩니다.

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

그리고 아마도 배치 "upert"와 같이 처음에 하고 싶었던 일을 하기 위해 Tcl을 사용하여 sql_update를 분할하고 개별 업데이트를 루프할 수 있습니다. 사전 성능에 미치는 영향은 매우 적습니다. http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php 을 참조하십시오.

가장 높은 비용은 코드에서 쿼리를 실행하는 것입니다. 데이터베이스 측면에서는 실행 비용이 훨씬 작습니다.

이 작업을 수행할 수 있는 간단한 명령어떠한 명령도 없습니다.

가장 올바른 접근법은 문서의 기능과 같은 기능을 사용하는 것입니다.

다른 솔루션(안전하지는 않지만)은 반환 시 업데이트를 수행하고 업데이트된 행을 확인한 후 나머지 행을 삽입하는 것입니다.

다음과 같은 것:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

ID:2가 반환되었다고 가정합니다.

insert into table (id, column) values (1, 'aa'), (3, 'cc');

물론 여기에 명확한 인종 조건이 있기 때문에 조만간 (동시 환경에서) 구제될 것이지만, 보통은 작동할 것입니다.

여기 이 주제에 대한 더 길고 포괄적인 기사가 있습니다.

병합 기능을 사용합니다.

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

개인적으로, 저는 삽입문에 첨부된 "규칙"을 설정했습니다.시간별로 고객별 DNS 조회 수를 기록한 "dns" 테이블이 있다고 가정합니다.

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

업데이트된 값이 있는 행을 다시 삽입하거나 값이 없는 경우 해당 행을 만들 수 있습니다.customer_id와 시간을 입력합니다.이와 같은 것:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Update: 동시 삽입이 발생할 경우 unique_violation 예외가 발생하므로 실패할 수 있습니다.그러나 종료되지 않은 트랜잭션은 계속 성공할 것이며 종료된 트랜잭션만 반복하면 됩니다.

그러나 항상 수많은 삽입이 발생하는 경우 삽입문 주위에 테이블 잠금을 배치해야 합니다. SHARE ROW EXCLIUSE 잠금은 대상 테이블에 행을 삽입, 삭제 또는 업데이트할 수 있는 작업을 방지합니다.그러나 고유 키를 업데이트하지 않는 업데이트는 안전하므로 작업을 수행하지 않을 경우 대신 권고 잠금을 사용하십시오.

또한 복사 명령은 규칙을 사용하지 않으므로 복사로 삽입할 경우 트리거를 대신 사용해야 합니다.

가장 선호하는 답변과 비슷하지만 약간 더 빨리 작동합니다.

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(출처: http://www.the-art-of-web.com/sql/upsert/)

삽입 및 교체를 원할 경우 위의 "upsert" 기능을 사용자 지정합니다.

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

실행한 후 다음과 같은 작업을 수행합니다.

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

컴파일러 오류를 방지하기 위해 이중 달러 쉼표를 넣는 것이 중요합니다.

속도 확인...

포스트그리에 따르면문의 SQL 문서, 처리ON DUPLICATE KEY대/소문자가 지원되지 않습니다.구문의 해당 부분은 독점적인 MySQL 확장입니다.

계정 설정을 이름 값 쌍으로 관리하는 것과 동일한 문제가 있습니다.설계 기준은 서로 다른 클라이언트가 서로 다른 설정 집합을 가질 수 있다는 것입니다.

JWP와 유사한 제 솔루션은 대량 삭제 및 교체하여 응용프로그램 내에서 병합 레코드를 생성하는 것입니다.

이것은 플랫폼에 독립적인 매우 안전하며 클라이언트당 약 20개 이상의 설정이 없기 때문에 로드가 상당히 낮은 DB 호출은 3개에 불과합니다. 아마도 가장 빠른 방법일 것입니다.

개별 행을 업데이트(예외를 확인한 후 삽입)하는 방법이나 일부 조합을 사용하는 방법은 비정상적인 코드로, 느리고 자주 중단됩니다. 위에서 언급한 바와 같이 비표준 SQL 예외 처리가 db에서 db로 변경되거나 릴리스에서 릴리스로 변경되기 때문입니다.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT

작은 세트를 병합하는 경우 위의 기능을 사용하면 됩니다.하지만, 만약 당신이 많은 양의 데이터를 병합하고 있다면, 저는 http://mbk.projects.postgresql.org 을 조사하는 것을 제안합니다.

현재의 모범 사례는 다음과 같습니다.

새/업데이트된 데이터를 임시 테이블에 복사(물론, 비용이 괜찮다면 INSERT를 수행할 수도 있습니다)
Acquire Lock [옵션](테이블 잠금, IMO보다 권장되는 조언)
병합. (재미있는 부분)

UPDATE는 수정된 행 수를 반환합니다.JDBC(Java)를 사용하는 경우 0에 대해 이 값을 확인하고 영향을 받는 행이 없으면 INSERT를 대신 실행할 수 있습니다.다른 프로그래밍 언어를 사용하는 경우, 수정된 행 수를 계속 구할 수도 있습니다. 설명서를 확인하십시오.

이것은 우아하지 않을 수도 있지만 호출 코드에서 사용하기에는 훨씬 간단한 SQL을 가지고 있습니다.이와 달리 PL/PSQL로 10줄 스크립트를 작성하는 경우에는 그것만을 위한 하나 또는 다른 종류의 유닛 테스트를 수행해야 합니다.

편집: 예상대로 작동하지 않습니다.승인된 답변과 달리 두 프로세스가 반복적으로 호출할 때 고유한 키 위반이 발생합니다.upsert_foo동시에

유레카! 나는 한 가지 질문으로 그것을 할 수 있는 방법을 찾았습니다: 사용.UPDATE ... RETURNING영향을 받은 행이 있는지 테스트하는 방법

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

그UPDATE안타깝게도 이것은 구문 오류이기 때문에 별도의 절차로 수행해야 합니다.

... WHERE NOT EXISTS (UPDATE ...)

이제 원하는 대로 작동합니다.

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

PostgreSQL >= v15

Postgre에서처럼 이 주제에 대한 큰 뉴스.SQL v15. 명령을 사용할 수 있습니다.실제로 오랫동안 기다려온 이 기능은 v15 릴리스의 개선 사항 중 첫 번째로 나열되었습니다.

은 이는다비슷다니합과와 .INSERT ... ON CONFLICT배치 지향적입니다.한 그은강니다력합것을 가지고 .WHEN MATCHED 대WHEN NOT MATCHED할 수 있는 능력을 주는 구조INSERT,UPDATE또는DELETE그런 조건으로

대량 변경을 용이하게 할 뿐만 아니라 전통적인 제어 기능까지 추가합니다.UPSERT그리고.INSERT ... ON CONFLICT

공식 페이지에서 다음과 같은 완전한 샘플을 확인하십시오.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

PostgreSQL v9, v10, v11, v12, v13, v14

이상인 에는 버이 v15 이고 v9.5 상전경을 사용하는 것이 .UPSERT 구문, 용사 사용ON CONFLICT.

다음은 특수 조건(때로는 제약 조건을 만들 수 없기 때문에 'on conflict'를 사용할 수 없는 경우가 있음)을 사용하여 특수한 sql 구문 없이 매개 변수를 사용하여 업버트하는 방법의 예입니다.

WITH upd AS
(
    update view_layer set metadata=:metadata where layer_id = :layer_id and view_id = :view_id returning id
)
insert into view_layer (layer_id, view_id, metadata)
(select :layer_id layer_id, :view_id view_id, :metadata metadata FROM view_layer l 
where NOT EXISTS(select id FROM upd WHERE id IS NOT NULL) limit 1)
returning id

아마도 도움이 될 것입니다.

언급URL : https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql

'programing' 카테고리의 다른 글

각각의 경우(VB.NET)에서 마지막 루프를 어떻게 찾습니까? (0)	2023.05.26
javascript/jQuery를 사용하여 구글 reCAPTCHA v2의 유효성을 확인하려면 어떻게 해야 합니까? (0)	2023.05.26
Git repo에 파일을 보관하지만 변경 내용을 추적하지 않음 (0)	2023.05.21
HTML 테이블에서 열을 숨기는 방법은 무엇입니까? (0)	2023.05.21
Swift에서 UI 버튼 이미지를 변경하는 방법 (0)	2023.05.21

현재글PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

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

PYTHON, asp.net, Excel, spring-boot, json, sql-server, Git, bash, jQuery, Oracle, ReactJS, MariaDB, typescript, R, ajax, wordpress, mongodb, C, angularJS, Android,

Today :
Yesterday :

abcjava

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

PostgreSQL >= v15

PostgreSQL v9, v10, v11, v12, v13, v14

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2025/01 »
일	월	화	수	목	금	토
			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	31

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

PostgreSQL에서 중복 업데이트 시 삽입하시겠습니까?

PostgreSQL >= v15

PostgreSQL v9, v10, v11, v12, v13, v14

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바