Programing

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

lottogame 2020. 10. 3. 09:46
반응형

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

이제 PostgreSQL로 전환했으며 분명히 이것은 올바르지 않습니다. 모든 올바른 테이블을 참조하고 있으므로 사용되는 다른 키워드의 문제라고 가정하지만 PostgreSQL 문서에서 이것이 다루는 곳이 확실하지 않습니다.

명확히하기 위해 몇 가지 항목을 삽입하고 이미 존재하는 경우 업데이트합니다.

버전 9.5 이후의 PostgreSQL 에는 ON CONFLICT 절이있는 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의 이메일 그룹 아카이브에서 "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;

더 명확한 예는 a_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"인 행이 이미 존재하면 UPDATE가 성공하고 그렇지 않으면 아무 효과가 없습니다.

INSERT는 "id = 3"인 행이 아직없는 경우에만 성공합니다.

이 두 가지를 단일 문자열로 결합하고 애플리케이션에서 실행되는 단일 SQL 문으로 둘 다 실행할 수 있습니다. 단일 트랜잭션에서 함께 실행하는 것이 좋습니다.

이는 분리 또는 잠긴 테이블에서 실행될 때 매우 잘 작동하지만, 행이 동시에 삽입되는 경우 중복 키 오류로 여전히 실패하거나 행이 동시에 삭제 될 때 행이 삽입되지 않고 종료 될 수 있음을 의미하는 경쟁 조건의 영향을받습니다. . SERIALIZABLE의 PostgreSQL 9.1 이상에 거래는 많이 시도해야 의미 매우 높은 직렬화 실패율의 비용으로 안정적으로 처리합니다. 참조 upsert 너무 복잡 이유를 자세히이 경우에 대해 설명한다.

이 방법은 또한 에 손실 업데이트에 따라 read committed응용 프로그램을 확인하지 않는 한 영향을받는 행 수와를 검증 중 하나를 분리 insert또는 update영향을받는 행 .

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에서 Craig Ringer
의 후속 조치를 참조하십시오.

PostgreSQL 9.5 이상에서는 INSERT ... ON CONFLICT UPDATE.

설명서를 참조하십시오 .

MySQL INSERT ... ON DUPLICATE KEY UPDATE은 직접 ON CONFLICT 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;

차이점 :

  • 당신은 해야한다 고유성 검사에 사용하는 열 이름 (또는 고유 제한 조건 이름)을 지정합니다. 그게ON CONFLICT (columnname) DO

  • 이 키워드 SET는 일반 UPDATE문인 것처럼 사용해야합니다 .

몇 가지 멋진 기능도 있습니다.

  • 당신은 당신의 WHERE조항을 가질 수 있습니다 UPDATE( 특정 값 ON CONFLICT UPDATE에 ON CONFLICT IGNORE대해 효과적으로 전환 할 수 있음 )

  • 삽입을 위해 제안 된 값은 EXCLUDED대상 테이블과 동일한 구조를 갖는 row-variable로 사용할 수 있습니다 . 테이블 이름을 사용하여 테이블의 원래 값을 가져올 수 있습니다. 그래서이 경우에 EXCLUDED.c있을 것입니다 10및 (즉, 우리가 삽입하려고 무엇 때문에) "table".c될 것입니다 3그 테이블의 현재 값이 때문입니다. SET표현식과 WHERE절 에서 둘 중 하나 또는 둘 다를 사용할 수 있습니다 .

upsert에 대한 배경 지식은 PostgreSQL에서 어떻게 UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE)를 참조합니까?

여기에 왔을 때 똑같은 것을 찾고 있었지만 일반적인 "upsert"기능이 없어서 약간 귀찮았 기 때문에 업데이트를 전달하고 해당 함수에 대한 인수로 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;
$$;

and perhaps to do what you initially wanted to do, batch "upsert", you could use Tcl to split the sql_update and loop the individual updates, the preformance hit will be very small see http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

the highest cost is executing the query from your code, on the database side the execution cost is much smaller

There is no simple command to do it.

The most correct approach is to use function, like the one from docs.

Another solution (although not that safe) is to do update with returning, check which rows were updates, and insert the rest of them

Something along the lines of:

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;

assuming id:2 was returned:

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

Of course it will bail out sooner or later (in concurrent environment), as there is clear race condition in here, but usually it will work.

Here's a longer and more comprehensive article on the topic.

Personally, I've set up a "rule" attached to the insert statement. Say you had a "dns" table that recorded dns hits per customer on a per-time basis:

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

You wanted to be able to re-insert rows with updated values, or create them if they didn't exist already. Keyed on the customer_id and the time. Something like this:

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: This has the potential to fail if simultaneous inserts are happening, as it will generate unique_violation exceptions. However, the non-terminated transaction will continue and succeed, and you just need to repeat the terminated transaction.

However, if there are tons of inserts happening all the time, you will want to put a table lock around the insert statements: SHARE ROW EXCLUSIVE locking will prevent any operations that could insert, delete or update rows in your target table. However, updates that do not update the unique key are safe, so if you no operation will do this, use advisory locks instead.

Also, the COPY command does not use RULES, so if you're inserting with COPY, you'll need to use triggers instead.

I custom "upsert" function above, if you want to INSERT AND REPLACE :

` 

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

And after to execute, do something like this : 

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

Is important to put double dollar-comma to avoid compiler errors

  • check the speed...

Similar to most-liked answer, but works slightly faster:

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)

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

I have the same issue for managing account settings as name value pairs. The design criteria is that different clients could have different settings sets.

My solution, similar to JWP is to bulk erase and replace, generating the merge record within your application.

This is pretty bulletproof, platform independent and since there are never more than about 20 settings per client, this is only 3 fairly low load db calls - probably the fastest method.

The alternative of updating individual rows - checking for exceptions then inserting - or some combination of is hideous code, slow and often breaks because (as mentioned above) non standard SQL exception handling changing from db to db - or even release to release.

 #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

According the PostgreSQL documentation of the INSERT statement, handling the ON DUPLICATE KEY case is not supported. That part of the syntax is a proprietary MySQL extension.

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

I use this function merge

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

For merging small sets, using the above function is fine. However, if you are merging large amounts of data, I'd suggest looking into http://mbk.projects.postgresql.org

The current best practice that I'm aware of is:

  1. COPY new/updated data into temp table (sure, or you can do INSERT if the cost is ok)
  2. Acquire Lock [optional] (advisory is preferable to table locks, IMO)
  3. Merge. (the fun part)

UPDATE will return the number of modified rows. If you use JDBC (Java), you can then check this value against 0 and, if no rows have been affected, fire INSERT instead. If you use some other programming language, maybe the number of the modified rows still can be obtained, check documentation.

This may not be as elegant but you have much simpler SQL that is more trivial to use from the calling code. Differently, if you write the ten line script in PL/PSQL, you probably should have a unit test of one or another kind just for it alone.

Edit: This does not work as expected. Unlike the accepted answer, this produces unique key violations when two processes repeatedly call upsert_foo concurrently.

Eureka! I figured out a way to do it in one query: use UPDATE ... RETURNING to test if any rows were affected:

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;

The UPDATE has to be done in a separate procedure because, unfortunately, this is a syntax error:

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

Now it works as desired:

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

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

반응형

'Programing' 카테고리의 다른 글

MySQL에서 작은 따옴표, 큰 따옴표 및 백틱을 사용하는 경우  (0) 2020.10.03
Python에서 GUID / UUID를 만드는 방법  (0) 2020.10.03
wget을 통해 Linux에서 Java JDK를 다운로드하면 대신 라이센스 페이지가 표시됩니다.  (0) 2020.10.03
다중 인덱스 vs 다중 열 인덱스  (0) 2020.10.03
대부분의 웹 브라우저에서 PUT, DELETE, HEAD 등의 방법을 사용할 수 있습니까?  (0) 2020.10.03

'Programing'의 다른글

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

관련글

댓글

티스토리툴바