PostgreSQL में डुप्लिकेट अद्यतन पर डालें?




upsert sql-merge (11)

कई महीने पहले मैंने स्टैक ओवरफ्लो पर एक उत्तर से सीखा है कि निम्नलिखित वाक्यविन्यास का उपयोग करके 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 दस्तावेज़ में यह कहां शामिल है।

स्पष्टीकरण के लिए, मैं कई चीजें डालना चाहता हूं और यदि वे पहले से ही अपडेट करने के लिए मौजूद हैं।


PostgreSQL 9.1 के साथ यह एक लिखने योग्य सीटीई ( सामान्य तालिका अभिव्यक्ति ) का उपयोग करके हासिल किया जा सकता है:

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 पर क्रेग रिंगर द्वारा अनुवर्ती देखें


PostgreSQL 9.5 और नए में आप INSERT ... ON CONFLICT UPDATE उपयोग कर सकते हैं INSERT ... ON CONFLICT UPDATE

दस्तावेज देखें।

एक MySQL INSERT ... ON DUPLICATE KEY UPDATE सीधे एक ON CONFLICT UPDATE जा सकता है। न तो एसक्यूएल-मानक वाक्यविन्यास है, वे दोनों डेटाबेस-विशिष्ट एक्सटेंशन हैं। इसके लिए 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 विवरण था

इसमें कुछ अच्छी विशेषताएं भी हैं:

  • आपके UPDATE पर एक WHERE क्लॉज हो सकता है (आपको कुछ मूल्यों के लिए ON CONFLICT IGNORE में ON CONFLICT UPDATE को प्रभावी ढंग से चालू करने देता है)

  • प्रस्तावित-प्रविष्टि मान मूल्य-परिवर्तनीय के रूप में उपलब्ध हैं, जिनकी लक्ष्य तालिका के समान संरचना है। आप टेबल नाम का उपयोग कर तालिका में मूल मान प्राप्त कर सकते हैं। तो इस मामले में EXCLUDED.c 10 होगा (क्योंकि यही वह है जिसे हमने सम्मिलित करने का प्रयास किया) और "table".c 3 होगा क्योंकि यह तालिका में वर्तमान मान है। आप SET अभिव्यक्तियों और WHERE खंड में दोनों या दोनों का उपयोग कर सकते हैं।

अप्सर्ट पर पृष्ठभूमि के लिए PostgreSQL में यूपीएसईआरटी (मेर्ज, इंसर्ट ... डिप्लिकेट अपडेट पर) कैसे देखें ?


ऐसा करने के लिए कोई आसान आदेश नहीं है।

सबसे सही तरीका फ़ंक्शन का उपयोग करना है, जैसे docs से एक।

एक और समाधान (हालांकि सुरक्षित नहीं है) लौटने के साथ अद्यतन करना है, जांचें कि कौन सी पंक्तियां अपडेट थीं, और बाकी के बाकी डालें

लाइनों के साथ कुछ:

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;

मानते हुए आईडी: 2 वापस कर दिया गया था:

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

बेशक यह जल्द या बाद में (समवर्ती वातावरण में) जमानत देगा, क्योंकि यहां स्पष्ट दौड़ की स्थिति है, लेकिन आमतौर पर यह काम करेगा।

यहां विषय पर एक लंबा और अधिक व्यापक लेख है


खाता मूल्य जोड़ों के रूप में खाता सेटिंग्स प्रबंधित करने के लिए मेरे पास एक ही समस्या है। डिजाइन मानदंड यह है कि विभिन्न ग्राहकों के पास अलग-अलग सेटिंग्स सेट हो सकते हैं।

जेडब्लूपी के समान मेरा समाधान थोक मिटाना और प्रतिस्थापित करना है, जो आपके आवेदन के भीतर मर्ज रिकॉर्ड उत्पन्न करता है।

यह सुंदर बुलेटप्रूफ, मंच स्वतंत्र है और चूंकि प्रति ग्राहक लगभग 20 सेटिंग्स से अधिक नहीं होते हैं, यह केवल 3 काफी कम लोड डीबी कॉल है - शायद सबसे तेज़ तरीका।

अलग-अलग पंक्तियों को अपडेट करने का विकल्प - अपवादों की जांच करने के बाद जांचना - या कुछ संयोजन घृणित कोड है, धीमा और अक्सर टूट जाता है क्योंकि (जैसा ऊपर बताया गया है) गैर मानक एसक्यूएल अपवाद हैंडलिंग डीबी से डीबी में बदल रहा है - या यहां तक ​​कि रिलीज करने के लिए भी रिलीज़।

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

और संभवतः जो करना आप करना चाहते थे, बैच "अप्सर्ट" करना चाहते हैं, आप एसक्यूएल_अपडेट को विभाजित करने और व्यक्तिगत अपडेट लूप करने के लिए टीसीएल का उपयोग कर सकते हैं, प्रीफॉर्मेंस हिट बहुत छोटा होगा http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

उच्चतम लागत आपके कोड से क्वेरी निष्पादित कर रही है, डेटाबेस पक्ष पर निष्पादन लागत बहुत छोटी है


मैं इस समारोह विलय का उपयोग करता हूं

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

व्यक्तिगत रूप से, मैंने सम्मिलित कथन से जुड़ा एक "नियम" स्थापित किया है। मान लें कि आपके पास एक "डीएनएस" तालिका है जो प्रति ग्राहक आधार पर प्रति ग्राहक डीएनएस हिट दर्ज की गई है:

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

आप अद्यतन मूल्यों के साथ पंक्तियों को फिर से सम्मिलित करने में सक्षम होना चाहते थे, या यदि वे पहले से मौजूद नहीं थे तो उन्हें बनाएं। ग्राहक_आईडी और समय पर कुंजी। कुछ इस तरह:

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

अद्यतन: यदि एक साथ आवेषण हो रहे हैं, तो इसमें असफल होने की संभावना है, क्योंकि यह अद्वितीय_विलेशन अपवाद उत्पन्न करेगा। हालांकि, गैर-समाप्त लेनदेन जारी रहेगा और सफल होगा, और आपको केवल समाप्त लेनदेन को दोहराने की जरूरत है।

हालांकि, अगर हर समय कई सारे इन्सर्ट होते हैं, तो आप सम्मिलित कथन के चारों ओर एक टेबल लॉक रखना चाहते हैं: शेयर पंक्ति अनन्य लॉकिंग किसी भी ऑपरेशन को रोक देगा जो आपकी लक्षित तालिका में पंक्तियों को सम्मिलित, हटा या अपडेट कर सकती है। हालांकि, अनन्य कुंजी अपडेट नहीं करने वाले अपडेट सुरक्षित हैं, इसलिए यदि आप कोई ऑपरेशन नहीं करेंगे, तो इसके बजाय सलाहकार ताले का उपयोग करें।

साथ ही, COPY कमांड नियमों का उपयोग नहीं करता है, इसलिए यदि आप COPY के साथ सम्मिलित हैं, तो आपको इसके बजाय ट्रिगर का उपयोग करना होगा।


संस्करण 9.5 के बाद PostgreSQL में UPSERT क्लॉज के साथ 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;

"अप्सर्ट" के लिए पोस्टग्रेस्क्ल के ईमेल समूह अभिलेखागार को खोजना मैन्युअल में, जो संभवतः आप करना चाहते हैं, उसका एक उदाहरण ढूंढने की ओर जाता है:

उदाहरण 38-2। अद्यतन / INSERT के साथ अपवाद

यह उदाहरण अद्यतन के रूप में अद्यतन या 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 और उससे ऊपर के सीटीई का उपयोग करके, थोक में इसे कैसे किया जाए, इसका संभवतः एक उदाहरण है:

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 का उत्तर देखें।


INSERT कथन के PostgreSQL दस्तावेज़ के अनुसार, ON DUPLICATE KEY केस को संभालना समर्थित नहीं है। सिंटैक्स का वह हिस्सा एक मालिकाना MySQL एक्सटेंशन है।


चेतावनी: यदि एक ही समय में कई सत्रों से निष्पादित किया गया है तो यह सुरक्षित नहीं है (नीचे चेतावनी देखें)।

Postgresql में "यूपीएसईआरटी" करने का एक और चालाक तरीका दो अनुक्रमिक अद्यतन / 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 कथन निष्पादित कर सकते हैं। एक ही लेनदेन में उन्हें एक साथ चलाने की अत्यधिक अनुशंसा की जाती है।

अलगाव या लॉक टेबल पर चलने पर यह बहुत अच्छी तरह से काम करता है, लेकिन दौड़ की स्थितियों के अधीन है जिसका अर्थ है कि यदि कोई पंक्ति एक साथ डाली जाती है तो यह डुप्लिकेट कुंजी त्रुटि के साथ असफल हो सकती है, या किसी पंक्ति को एक साथ हटाए जाने पर समाप्त हो सकती है । PostgreSQL 9.1 या उच्चतर पर एक SERIALIZABLE लेनदेन इसे बहुत उच्च क्रमबद्धता विफलता दर की लागत पर विश्वसनीय रूप से संभाल लेगा, जिसका अर्थ है कि आपको बहुत कुछ करना होगा। देखें कि इतनी जटिल क्यों अपरिवर्तित है , जो इस मामले पर अधिक विस्तार से चर्चा करता है।

यह दृष्टिकोण read committed अलगाव में खोए गए अपडेट के अधीन भी है जब तक कि एप्लिकेशन प्रभावित पंक्ति की गणना नहीं करता और सत्यापित करता है कि या तो insert या update एक पंक्ति को प्रभावित करता है


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




sql-merge