मान लीजिए मेरे पास इस तरह बनाई गई एक टेबल है:

CREATE TABLE actions (name text primary key, value text);
INSERT INTO actions (name) VALUES ('bla');

एक नई पंक्ति सम्मिलित करने का प्रयास करते समय, तीन संभावनाएँ होती हैं:

  1. 'नाम' के लिए कोई पंक्ति मौजूद नहीं है। नई पंक्ति डाली जानी चाहिए।
  2. 'नाम' के लिए एक पंक्ति मौजूद है जिसका मान NULL है। मौजूदा पंक्ति को नए मान के साथ अद्यतन किया जाना चाहिए।
  3. 'नाम' के लिए एक पंक्ति मौजूद है जिसका एक गैर-शून्य मान है। सम्मिलन विफल होना चाहिए।

मैंने सोचा कि मुझे इस तरह यूपीएसईआरटी सिंटैक्स का उपयोग करने में सक्षम होना चाहिए:

INSERT INTO actions (name, value) values ('bla', 'val1')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

INSERT INTO actions (name, value) values ('bla', 'val2')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

पहला आदेश मौजूदा पंक्ति को अपेक्षित रूप से अद्यतन करता है। लेकिन दूसरी कमांड पंक्ति को फिर से अपडेट करती है, जिसकी मुझे उम्मीद नहीं थी।

संघर्ष लक्ष्य में वह क्लॉज क्या है जिसे करना चाहिए? मेरे परीक्षणों में इससे कोई फर्क नहीं पड़ता कि यह सही या गलत का मूल्यांकन करता है।

पर आधारित एक प्राथमिक कुंजी की, लेकिन फिर मैं एक ही नाम के साथ कई पंक्तियों के साथ समाप्त होता हूं।

1
Schelte Bron 13 अक्टूबर 2019, 20:43

2 जवाब

सबसे बढ़िया उत्तर

सेकंड WHERE का उपयोग करने से आप जो चाहें करेंगे:

INSERT INTO actions(name) VALUES ('bla');
INSERT INTO actions(name, value) VALUES ('bla', 'val1')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1
INSERT INTO actions(name, value) VALUES ('bla', 'val2')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1

विरोध कॉलम के बाद पहला WHERE, विरोधों का पता लगाने के लिए उपयोग करने के लिए एक विशेष आंशिक अनुक्रमणिका का चयन करने के लिए है। आपको इसका अनुभव देने के लिए एक उदाहरण:

sqlite> CREATE TABLE ex(name, value1, value2);
sqlite> CREATE UNIQUE INDEX ex_idx_name ON ex(name) WHERE value1 IS NULL;
sqlite> INSERT INTO ex(name) VALUES ('bla');
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val1');
Error: UNIQUE constraint failed: ex.name
sqlite> INSERT INTO ex(name, value1, value2) VALUES ('bla', 1, 'val2');
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      (null)
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val3')
   ...> ON CONFLICT(name) WHERE value1 IS NULL DO UPDATE SET value2 = excluded.value2;
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      val3
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val4')
   ...> ON CONFLICT(name) DO UPDATE SET value2 = excluded.value2;
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

विशेष रूप से अंतिम दो INSERT पर ध्यान दें: पहला एक शून्य मान के साथ पंक्ति को अद्यतन करता है, और दूसरा एक त्रुटि उत्पन्न करता है क्योंकि यह पूरी तरह से एक अद्वितीय अनुक्रमणिका निर्दिष्ट नहीं करता है।

3
Shawn 13 अक्टूबर 2019, 23:51
यह वही नहीं करता जो मैं चाहता हूं, क्योंकि यह स्थिति 3 में विफल नहीं होता है। यह चुपचाप आदेश को अनदेखा करता है। हालाँकि, मेरे साथ ऐसा हुआ कि मैं ट्रिगर का उपयोग करके स्थिति 3 में विफलता प्राप्त कर सकता था। फिर एक नियमित अप्सर्ट 1 और 2 स्थितियों के लिए करेगा। लेकिन अप्सर्ट को समझाने के लिए धन्यवाद जहां क्लॉज, जो वास्तविक प्रश्न था।
 – 
Schelte Bron
14 अक्टूबर 2019, 21:03

यह मेरे लिए एक बग की तरह दिखता है। लेकिन आप आसानी से इसके आसपास काम कर सकते हैं:

INSERT INTO actions (name, value) values ('bla', 'val1')
ON CONFLICT(name) 
DO UPDATE SET value = COALESCE(value, excluded.value);

यहां एक db<>fiddle है।

1
Gordon Linoff 13 अक्टूबर 2019, 20:51