복붙노트

[SQL] 삽입, PostgreSQL의 중복 업데이트에 대한?

SQL

삽입, PostgreSQL의 중복 업데이트에 대한?

나는 전에 몇 달은 다음 구문을 사용하여 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);

지금의 PostgreSQL로 전환 한 분명히이 올바르지 않습니다. 나는 그것이 다른 키워드의 문제가 사용되고 가정 있도록 모든 올바른 테이블을 참조 있어요하지만 PostgreSQL의 설명서에이 적용되는 경우 잘 모르겠어요.

명확히하기 위해, 나는 몇 가지를 삽입 할 그들이 이미 업데이트하기 위해 존재합니다.

해결법

  1. ==============================

    1.PostgreSQL는 버전 9.5 ON 충돌 절 UPSERT 구문을 가지고 있기 때문이다. 다음 구문 (MySQL의 유사)

    PostgreSQL는 버전 9.5 ON 충돌 절 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;

    당신이 가능하게 매뉴얼에,하고 싶은 일을하는 예를 찾는 "upsert"리드에 대한 PostgreSQL을의 전자 메일 그룹의 아카이브를 검색 :

    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 이상에서는 열팽창 계수를 사용하여 대량으로이 작업을 수행하는 방법의 예 가능성이있다 :

    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;

    명확한 예를 들어 a_horse_with_no_name의 답변을 참조하십시오.

  2. ==============================

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

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

    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"인 행이 이미 존재하는 경우 UPDATE 그렇지 않으면 효과가 없습니다, 성공합니다.

    와 행 "ID가 = 3"이미 존재하지 않는 경우에만 INSERT가 성공합니다.

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

    이것은 아주 잘 분리 또는 잠긴 테이블에 실행할 때 작동하지만,이 행이 동시에 삽입하면 여전히 중복 키 오류로 실패 할 수 있습니다, 또는 행이 동시에 삭제되는 경우가 삽입되지 행과 종료 할 수있는 의미 경쟁 조건이 적용됩니다 . PostgreSQL을 9.1 이상에 SERIALIZABLE 트랜잭션은 많이 시도해야 의미 매우 높은 직렬화 실패율의 비용으로 안정적으로 처리합니다. upsert 그래서 더 자세하게이 사건을 설명하는, 복잡 이유를 참조하십시오.

    이 방법은 또한 응용 프로그램을 확인 영향을받는 행의 수와 삽입 또는 업데이트 중 하나가 행에 영향을하는지 확인하지 않는 한 커밋 된 읽기 격리에서 업데이트 손실을 받는다.

  3. ==============================

    3.PostgreSQL의 9.1이 쓰기 가능한 CTE (공통 테이블 식)을 사용하여 달성 될 수있다 :

    PostgreSQL의 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에 크레이그 벨소리로 추적을 참조하십시오

  4. ==============================

    4.PostgreSQL의 9.5 및 최신에서 당신은 갈등 Update에 ... INSERT를 사용할 수 있습니다.

    PostgreSQL의 9.5 및 최신에서 당신은 갈등 Update에 ... INSERT를 사용할 수 있습니다.

    설명서를 참조하십시오.

    MySQL의 INSERT은 ... ON DUPLICATE KEY UPDATE 직접 ON 충돌 UPDATE로 다시 표현 될 수있다. 나도 그들이 모두 데이타베이스 고유의 확장있어, 표준 SQL 구문입니다. MERGE이 사용되지 않은 좋은 이유가 있습니다, 새로운 구문은 단지 재미를 위해 작성되지 않았습니다. (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;

    차이 :

    그것은 몇 가지 좋은 너무 기능이 있습니다 :

    배경 upsert 볼에 어떻게 PostgreSQL의에서 UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE)에?

  5. ==============================

    5.내가 여기 왔을 때 나는 같은 일을 찾고 있었어요,하지만 난 그냥 업데이트를 통과하고 그 함수의 형태로 매뉴얼을에 인수로 SQL 삽입 할 수 있다고 생각 있도록 "upsert"일반의 부족이 기능은 나에게 조금 botherd

    내가 여기 왔을 때 나는 같은 일을 찾고 있었어요,하지만 난 그냥 업데이트를 통과하고 그 함수의 형태로 매뉴얼을에 인수로 SQL 삽입 할 수 있다고 생각 있도록 "upsert"일반의 부족이 기능은 나에게 조금 botherd

    다음과 같이 것이다 :

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

    아마도 당신은의 preformance 히트 http://archives.postgresql.org/pgsql-를 볼 아주 작은 될 것 sql_update 및 루프 개별 업데이트를 분할은 Tcl을 사용할 수 있습니다, 당신이 처음 배치 "upsert"을하고 싶었던 일을하는 성능 / 2006-04 / msg00557.php

    실행 비용이 훨씬 작은 쪽면을 가장 높은 비용은 데이터베이스에 코드에서 쿼리를 실행

  6. ==============================

    6.그것을 할 수있는 간단한 명령은 없습니다.

    그것을 할 수있는 간단한 명령은 없습니다.

    가장 정확한 방법은 문서에서처럼 기능을 사용하는 것입니다.

    또 다른 해결책은 (비록 안전하지 그), 행 업데이트를했다 수표를 반환으로 업데이 트를 수행하고 나머지를 삽입하는 것입니다

    의 라인을 따라 뭔가 :

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

    물론 여기에서 분명한 경쟁 조건이 존재하는 한 그것은, 조만간 (동시 환경에서) 구제하지만, 보통은 작동합니다.

    여기에 주제에 대한 더 길고 더 포괄적 인 문서입니다.

  7. ==============================

    7.개인적으로, 나는 삽입 문에 부착 된 "규칙"을 설정했습니다. 당신이 당 시간을 기준으로 고객 당 DNS 히트를 기록하는 "DNS"테이블을 가지고 말 :

    개인적으로, 나는 삽입 문에 부착 된 "규칙"을 설정했습니다. 당신이 당 시간을 기준으로 고객 당 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));

    업데이트 :이 동시 삽입이 발생하는 경우가 unique_violation 예외를 생성하므로, 실패 할 가능성이있다. 그러나, 비 종료 트랜잭션이 계속 성공하고, 당신은 종료 된 트랜잭션을 반복해야합니다.

    모든 시간을 벌어 삽입의 톤이 존재하는 경우, 당신은 삽입 문 주위에 테이블 잠금을 넣어하는 것이 좋습니다 : SHARE ROW EXCLUSIVE 잠금 삽입 목표 테이블에서 삭제 또는 업데이트 된 행 수있는 모든 작업을 방지 할 수 있습니다. 그러나, 고유 키를 업데이트하지 않는 업데이트가 더 작업이이 작업을 수행하지 않을 경우 그래서, 대신 자문 잠금을 사용하여 안전합니다.

    또한, COPY 명령은 너무 COPY와 당신이있는 거 삽입하는 경우, 대신 트리거를 사용해야합니다, 규칙을 사용하지 않습니다.

  8. ==============================

    8.나는 정의 위의 함수 "upsert", 당신은 INSERT 및 교체 할 경우 :

    나는 정의 위의 함수 "upsert", 당신은 INSERT 및 교체 할 경우 :

    `

     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... $$)

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

  9. ==============================

    9.나는이 기능 병합을 사용하여

    나는이 기능 병합을 사용하여

    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
  10. ==============================

    10.마찬가지로 답을 가장 좋아하지만, 약간 빠른 작동합니다 :

    마찬가지로 답을 가장 좋아하지만, 약간 빠른 작동합니다 :

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

  11. ==============================

    11.나는 이름 값 쌍으로 계정 설정을 관리 같은 문제가 있습니다. 설계 기준은 다른 클라이언트가 다른 설정 세트를 가질 수 있다는 것입니다.

    나는 이름 값 쌍으로 계정 설정을 관리 같은 문제가 있습니다. 설계 기준은 다른 클라이언트가 다른 설정 세트를 가질 수 있다는 것입니다.

    JWP와 유사한 내 솔루션, 응용 프로그램 내 병합 기록을 생성, 대량 삭제 및 교체하는 것입니다.

    이 꽤 방탄, 플랫폼 독립적이며 결코 이상의 클라이언트 당 약 20 설정과이 없기 때문에, 이것은 단지 3 매우 낮은 부하 DB 호출입니다 - 아마 가장 빠른 방법.

    다음 예외를 확인 삽입 - - 개별 행을 업데이트하는 대안 또는 일부 조합은 무시 무시한 속도가 느린 코드 때문에 (위에서 언급 한 바와 같이) 자주 휴식 DB에 DB에서 변경 처리가 아닌 표준 SQL 예외 - 또는 해제 놓습니다.

     #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
  12. ==============================

    12.를 ON DUPLICATE KEY 케이스를 처리하는 INSERT 문의 PostgreSQL의 설명서를 따라하는 것은 지원되지 않습니다. 구문의 그 부분은 독점적 인 MySQL의 확장이다.

    를 ON DUPLICATE KEY 케이스를 처리하는 INSERT 문의 PostgreSQL의 설명서를 따라하는 것은 지원되지 않습니다. 구문의 그 부분은 독점적 인 MySQL의 확장이다.

  13. ==============================

    13.

    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
  14. ==============================

    14.작은 세트를 병합 상기 함수를 사용 괜찮지. 당신은 많은 양의 데이터를 병합하는 경우, 나는 http://mbk.projects.postgresql.org로 보는 게 좋을 것

    작은 세트를 병합 상기 함수를 사용 괜찮지. 당신은 많은 양의 데이터를 병합하는 경우, 나는 http://mbk.projects.postgresql.org로 보는 게 좋을 것

    I는 알고 있어요 것이 현재 가장 좋은 방법 :

  15. ==============================

    15.UPDATE 수정 행의 수를 반환합니다. 당신이 JDBC (자바)를 사용하는 경우에는 행 대신, 화재 INSERT 영향되어 있지 않은 경우, 당신은 다음, 0에 대해이 값을 확인 할 수 있습니다. 당신은 다른 프로그래밍 언어를 사용하는 경우, 아마도 수정 된 행의 수는 아직 확인 설명서를 얻을 수 있습니다.

    UPDATE 수정 행의 수를 반환합니다. 당신이 JDBC (자바)를 사용하는 경우에는 행 대신, 화재 INSERT 영향되어 있지 않은 경우, 당신은 다음, 0에 대해이 값을 확인 할 수 있습니다. 당신은 다른 프로그래밍 언어를 사용하는 경우, 아마도 수정 된 행의 수는 아직 확인 설명서를 얻을 수 있습니다.

    이 우아한로하지 않을 수 있습니다하지만 당신은 호출 코드에서 사용하는 것이 더 간단하다 훨씬 간단 SQL 있습니다. 당신은 PL / PSQL에서 열 줄 스크립트를 작성하는 경우 다르게, 당신은 아마 혼자 종류의 단지에 대한 단위 하나의 시험 또는 다른이 있어야합니다.

  16. ==============================

    16.편집 : 예상대로이 작동하지 않습니다. 허용 대답과는 달리,이 두 과정이 반복 upsert_foo 동시에 전화 고유 키 위반을 생성합니다.

    편집 : 예상대로이 작동하지 않습니다. 허용 대답과는 달리,이 두 과정이 반복 upsert_foo 동시에 전화 고유 키 위반을 생성합니다.

    유레카! 모든 행이 영향을받은 경우 테스트로 돌아 ... 사용 UPDATE : 나는 하나의 쿼리에서 그것을 할 수있는 방법을 알아 낸 :

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

    17. from https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 엔티티 프레임 워크에 삽입하는 가장 빠른 방법  (0) 2020.03.06
[SQL] 빠른 600K 행에서 10 개 임의의 행을 선택 MYSQL  (0) 2020.03.06
[SQL] 어떻게 주문 후 오라클 쿼리에 의해 반환 된 행의 수를 제한 하는가?  (0) 2020.03.06
[SQL] 오류 MySQL의에 쿼리를 실행할 때 ONLY_FULL_GROUP_BY 관련  (0) 2020.03.06
[SQL] 어떻게 매개 변수화 된 SQL 쿼리를 만들려면 어떻게해야합니까? 왜해야 내가?  (0) 2020.03.06
,

티스토리툴바