PostgreSQL ON CONFLICT DO UPDATE को सामान्य “किसी भी एक अद्वितीय कुंजी से मिलान” ऑपरेशन के रूप में इस्तेमाल नहीं कर सकता। एक निर्णायक कुंजी चुनें, या लेन-देन में दोनों कुंजियों की स्पष्ट रूप से जाँच करें और उन इनपुट को अस्वीकार करें जो दो अलग-अलग पंक्तियों की पहचान करते हैं।

यह ट्यूटोरियल Mac पर Terminal में काम कर रहे डेवलपर को node-postgres के साथ दूसरी नीति लागू करने में मदद करता है: जब कोई भी कुंजी मेल न खाए तो प्रविष्टि जोड़ें, ठीक एक लेख मेल खाए तो उसे अपडेट करें, और जब कुंजियाँ अलग-अलग लेखों की ओर इशारा करें तो स्पष्ट टकराव लौटाएँ। सभी पंक्तियाँ कृत्रिम हैं।

SQL के उदाहरण PostgreSQL 18 के लिए हैं, जो प्राप्त किए गए वर्तमान दस्तावेज़ों में दिया गया संस्करण है। इस कार्यान्वयन में दो तत्काल अद्वितीय बाधाओं वाली सामान्य, गैर-विभाजित तालिका इस्तेमाल होती है। किसी दूसरे संस्करण के मैनुअल से उदाहरण अपनाने से पहले अपने सर्वर का प्रमुख संस्करण जाँचें। PostgreSQL 18 INSERT दस्तावेज़

दो अद्वितीय बाधाएँ एक टकराव लक्ष्य क्यों नहीं बन सकतीं

मान लें कि किसी लेख का एक बाहरी संदर्भ और एक स्लग है। दोनों अलग-अलग अद्वितीय होने चाहिए। किसी खाली प्रदर्शन डेटाबेस में यह पूरी सेटअप शुरू करें; इसे setup.sql नाम से सहेजें।

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

ये घोषणाएँ दो अलग-अलग नियम लागू करती हैं। इन्हें UNIQUE (external_key, slug) से बदलने पर मॉडल बदल जाएगा: केवल दोनों का युग्म अद्वितीय होना चाहिए, जिससे अलग-अलग संयोजनों में बाहरी कुंजियाँ और स्लग दोहराए जा सकेंगे। दोनों कॉलम को NOT NULL रखने से इस उदाहरण में nullable कुंजियों का व्यवहार भी नहीं आता। PostgreSQL बाधाएँ

DO UPDATE के लिए PostgreSQL को टकराव लक्ष्य चाहिए। कॉलम सूची मिलान करने वाले अद्वितीय इंडेक्स के कॉलम बताती है; इसका अर्थ “दोनों में से कोई भी कॉलम” नहीं है। इसलिए, ON CONFLICT (external_key, slug) ऊपर दी गई दो अलग-अलग बाधाओं से arbiter का अनुमान नहीं लगा सकता। किसी बाधा का नाम देने से वही बाधा चुनी जाती है, और सिंटैक्स वैकल्पिक हैंडलरों की श्रृंखला के बजाय एक ON CONFLICT क्लॉज़ देता है। PostgreSQL INSERT: ON CONFLICT क्लॉज़

गहरी समस्या यह तय करना है कि आने वाली पंक्ति किस मौजूदा लेख का प्रतिनिधित्व करती है। आने वाले मान feed-101 और green-notes पर विचार करें: बाहरी कुंजी नीले लेख की है, जबकि स्लग हरे लेख का है। दोनों आने वाली कुंजियों से किसी भी पंक्ति को अपडेट करने पर दूसरी पंक्ति से टकराव होगा। पहला मिलान चुनने से इनपुट का अनसुलझा निर्णय छिप जाएगा।

तय करें कि मिलान के हर संयोजन पर क्या होना चाहिए

क्वेरी लिखने से पहले नीचे दी गई निर्णय तालिका इस्तेमाल करें। यह इस स्कीमा से निकली प्रस्तावित एप्लिकेशन नीति है, PostgreSQL का अपने-आप दिया हुआ व्यवहार नहीं।

आने वाली बाहरी कुंजी आने वाला स्लग अलग-अलग मिलान वाली पंक्तियाँ इस ट्यूटोरियल में कार्रवाई
अप्रयुक्त अप्रयुक्त 0 लेख जोड़ें
किसी लेख से मेल खाती है अप्रयुक्त 1 स्लग सहित उस लेख को अपडेट करें
अप्रयुक्त किसी लेख से मेल खाता है 1 बाहरी कुंजी सहित उस लेख को अपडेट करें
लेख A से मेल खाती है लेख A से मेल खाता है 1 लेख A को अपडेट करें
लेख A से मेल खाती है लेख B से मेल खाता है 2 इनपुट अस्वीकार करें; दोनों लेख सुरक्षित रखें

एक-मिलान वाले मामलों में जानबूझकर किसी भी कुंजी से लेख की पहचान की जा सकती है और दूसरी कुंजी बदली जा सकती है। पुष्टि करें कि आपके इम्पोर्ट में यही व्यवहार चाहिए। यदि बाहरी कुंजी कभी नहीं बदलनी चाहिए, तो केवल स्लग से मिलने पर एप्लिकेशन टकराव दिखाना चाहिए।

यदि आपका वास्तविक नियम सरल है—“बाहरी कुंजी हमेशा लेख तय करती है”—तो सामान्य लक्षित अपसर्ट इस्तेमाल करें। यह पूरा कथन ऊपर की सेटअप के बाद चलता है:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

यहाँ EXCLUDED प्रस्तावित मान देता है। दूसरी अद्वितीय बाधा भी लागू रहती है: प्रस्तावित स्लग को green-notes में बदलने पर स्वतंत्र अद्वितीयता टकराव होगा, न कि दूसरे लेख पर स्विच। लक्षित अपसर्ट की परमाण्विक प्रविष्टि-या-अपडेट गारंटी स्वतंत्र त्रुटियों के न होने पर निर्भर करती है। PostgreSQL INSERT दस्तावेज़

समवर्ती लेखन के दौरान “किसी भी कुंजी” वाला निर्णय स्थिर रखें

खोज और उसके बाद प्रविष्टि करने के बीच एक अंतराल बनता है। कोई अन्य लेन-देन उस अंतराल में मेल खाने वाली कुंजी जोड़ सकता है। खोज में FOR UPDATE जोड़ने से प्राप्त पंक्तियाँ लॉक होती हैं; जब कोई पंक्ति मेल नहीं खाती, तो लॉक करने के लिए कोई प्राप्त पंक्ति नहीं होती। इसलिए, केवल पंक्ति लॉक करना इस विशेष शून्य-मिलान वाले रास्ते के लिए पर्याप्त नहीं है। PostgreSQL स्पष्ट लॉकिंग

छोटे इम्पोर्ट या कभी-कभार होने वाले डेस्कटॉप ऑपरेशन के लिए, दोनों कुंजियों की जाँच से पहले SHARE ROW EXCLUSIVE टेबल लॉक लेना एक सीधा समाधान है। यह मोड सामान्य प्रविष्टियों, अपडेट और डिलीट से लिए जाने वाले ROW EXCLUSIVE लॉक के साथ टकराता है, और इसी मोड के दूसरे लॉक से भी टकराता है। सामान्य रीड इसके साथ संगत रहते हैं। PostgreSQL टेबल-स्तरीय लॉक मोड

इसके बाद का क्रम है: लेन-देन शुरू करें, टेबल लॉक लें, अलग-अलग मिलान वाली पंक्तियाँ खोजें, निर्णय तालिका लागू करें और कमिट करें। स्पष्ट लॉक असंगत लॉक के लिए प्रतीक्षा करता है और लेन-देन समाप्त होने तक बना रहता है। PostgreSQL LOCK

यहाँ स्पष्ट रूप से READ COMMITTED इस्तेमाल करें। इसकी खोज शुरू होने से पहले कमिट किया गया डेटा दिखता है, जिसमें लॉक लेने की प्रतीक्षा के दौरान पूरे हुए लेखन भी शामिल हैं। इस स्नैपशॉट व्यवहार और प्राप्त टेबल लॉक को मिलाने से इस कार्यान्वयन को निर्णय लेकर लिखने का स्थिर आधार मिलता है। PostgreSQL लेन-देन पृथक्करण

यह एक व्यापक लॉक है: असंबंधित लेखों के लेखन भी प्रतीक्षा करते हैं। लेन-देन छोटा रखें; लॉक लेने और कमिट करने के बीच कोई संवादात्मक प्रॉम्प्ट या बाहरी काम न करें। बार-बार होने वाले समवर्ती इम्पोर्ट के लिए, इस डिज़ाइन को अपनाने से पहले विचार करें कि क्या एक canonical कुंजी पहचान तय कर सकती है।

node-postgres के साथ लेन-देन लागू करें

आपको चल रहा PostgreSQL 18 प्रदर्शन डेटाबेस, psql कमांड और आपके इंस्टॉल किए गए node-postgres पैकेज द्वारा समर्थित Node.js संस्करण चाहिए। Terminal में पैकेज इंस्टॉल करें और सेटअप फ़ाइल लोड करें। पहला कमांड पैकेज मेटाडेटा बनाता है, यदि आपकी प्रदर्शन डायरेक्टरी में वह पहले से मौजूद न हो। node-postgres इंस्टॉलेशन और संगतता

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

ये कमांड मानते हैं कि आपकी सामान्य स्थानीय कनेक्शन सेटिंग पहले से प्रदर्शन डेटाबेस चुनती हैं। प्रोग्राम new Pool() इस्तेमाल करता है, जो वही PostgreSQL कनेक्शन पर्यावरण चर पढ़ता है जो psql इस्तेमाल करता है; ज़रूरत हो तो PGDATABASE को अपने प्रदर्शन डेटाबेस के नाम पर सेट करें। node-postgres कनेक्ट करना

इस पूरे प्रोग्राम को upsert-articles.mjs नाम से सहेजें। यह मानता है कि सेटअप तालिका में कोई अतिरिक्त ट्रिगर या नियम नहीं हैं जो उसके मिलान या अपडेट के व्यवहार को बदलें। मान SQL टेक्स्ट से अलग, स्थितीय पैरामीटर के रूप में दिए जाते हैं। node-postgres क्वेरियाँ

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

node-postgres के अनुसार, हर लेन-देन कथन के लिए वही चेक-आउट क्लाइंट इस्तेमाल होता है। इन कथनों को अलग-अलग pool.query() कॉल के ज़रिए भेजने से कनेक्शन की यह आवश्यकता पूरी नहीं होगी। node-postgres लेन-देन

finally ब्लॉक हमेशा प्राप्त क्लाइंट को रिलीज़ करता है। यदि रोलबैक भी विफल हो जाए, तो release(true) कनेक्शन को हटा देता है। डेस्कटॉप स्क्रिप्ट खत्म होने पर बाहरी pool.end() पूल बंद करता है। node-postgres Pool API

प्रोग्राम को node upsert-articles.mjs से चलाएँ। अपेक्षित नतीजा संशोधित शीर्षक और अपरिवर्तित कुंजियों वाला नीला लेख है। JavaScript सिंटैक्स को Node.js के साथ जाँचा गया; डेटाबेस उदाहरणों को दस्तावेज़ों से मिलाकर देखा गया, लेकिन यहाँ PostgreSQL सर्वर पर चलाया नहीं गया।

हर शाखा जाँचें और विफलताओं को स्पष्ट रूप से संभालें

सेटअप की पंक्तियों का इस्तेमाल करते हुए, upsertArticle() को दिए गए ऑब्जेक्ट में बदलाव करके नीचे दिए गए मामले आज़माएँ। हर मामला मूल सेटअप स्थिति पर चलाएँ, या पहले के मामलों से हुए बदलावों का हिसाब रखें।

externalKey slug अपेक्षित नतीजा
feed-303 amber-notes नया लेख जोड़ें
feed-101 blue-revised नीले लेख को अपडेट करें और उसका स्लग बदलें
feed-303 blue-notes नीले लेख को अपडेट करें और उसकी बाहरी कुंजी बदलें
feed-101 blue-notes नीले लेख का शीर्षक अपडेट करें
feed-101 green-notes ARTICLE_KEY_CONFLICT थ्रो करें; किसी लेख को न बदलें

खोज पंक्तियाँ लौटाती है, इसलिए दोनों शर्तों से मेल खाने वाला लेख एक ही बार गिना जाता है। इन दो स्वतंत्र, गैर-नल अद्वितीय कुंजियों के साथ, अधिकतम दो अलग-अलग पंक्तियाँ मेल खा सकती हैं। कोड कभी भी LIMIT 1 इस्तेमाल करके, किसी लेख को हटाकर या इनपुट को चुपचाप छोड़कर दो-पंक्ति वाले नतीजे का निपटारा नहीं करता।

ARTICLE_KEY_CONFLICT एप्लिकेशन द्वारा तय किया गया कोड है। PostgreSQL का 23505 मतलब unique_violation है; यह एक अलग स्थिति है। यदि उदाहरण अपनाने के बाद दूसरा कोड दिखाई दे, तो हर डुप्लिकेट को एक जैसा नतीजा मानने के बजाय उल्लंघित बाधा और जोड़े गए किसी भी टेबल व्यवहार की जाँच करें। PostgreSQL संदेश के टेक्स्ट को पार्स करने के बजाय त्रुटि कोड जाँचने की सलाह देता है। PostgreSQL त्रुटि कोड

लॉक हासिल करने में प्रतीक्षा हो सकती है। यदि इस डेस्कटॉप कार्य में आप तुरंत विफलता चाहते हैं, तो लॉकिंग कथन को LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT से बदलें। तब PostgreSQL लॉक तुरंत हासिल न कर पाने पर त्रुटि देता है और प्रोग्राम रोलबैक पथ अपनाता है। PostgreSQL LOCK

क्या DO NOTHING या MERGE से काम आसान होगा?

बिना लक्ष्य वाला ON CONFLICT DO NOTHING किसी भी उपयोग योग्य अद्वितीय बाधा या इंडेक्स से टकराने वाली प्रविष्टि को छोड़ सकता है। इसे तब चुनें जब छोड़ना ही अपेक्षित नतीजा हो; यह यहाँ विकसित अपडेट नीति लागू नहीं करता। PostgreSQL INSERT दस्तावेज़

MERGE PostgreSQL 15 में पेश किया गया था। हालाँकि यह किसी भी कुंजी से जुड़ी जॉइन शर्त की अनुमति देता है, फिर भी उस जॉइन को उस स्रोत पंक्ति की नीति चाहिए जो दो अलग-अलग लक्ष्य लेखों से मेल खाती हो। इसकी कार्रवाइयाँ सामान्य बाधाओं के अधीन रहती हैं, और इसका समवर्ती व्यवहार लक्षित अपसर्ट के समान नहीं है। PostgreSQL 15 रिलीज़ नोट्स, PostgreSQL MERGE

“किसी भी कुंजी” वाले काम में, मिलानों की संख्या को स्पष्ट निर्णय बनाएँ: शून्य पर प्रविष्टि जोड़ें, एक पर अपडेट करें और दो पर उपयोगी टकराव संदेश के साथ रोकें। अद्वितीय बाधाएँ बनाए रखें और ऐसी समवर्ती रणनीति चुनें जो लेखन पूरा होने तक उस निर्णय को सुरक्षित रखे। जब लेखनों को क्रमबद्ध करना स्वीकार्य हो, तो टेबल-लॉक कार्यान्वयन एक ठोस शुरुआती बिंदु देता है।

स्रोत