DBA: समक्रमण आणि आयात सक्षमपणे आयोजित करा

मोठ्या डेटा सेटच्या जटिल प्रक्रियेसाठी (भिन्न ETL प्रक्रिया: आयात, रूपांतरणे आणि बाह्य स्रोतासह समक्रमण) अनेकदा गरज असते तात्पुरते "लक्षात ठेवा" आणि त्वरित प्रक्रिया करा काहीतरी प्रचंड.

या प्रकारचे एक सामान्य कार्य सहसा असे काहीतरी वाटते: "इथे लेखा विभाग क्लायंट बँकेतून उतरवला शेवटची प्राप्त झालेली देयके, तुम्हाला ती वेबसाइटवर त्वरीत अपलोड करणे आणि तुमच्या खात्यांशी लिंक करणे आवश्यक आहे.”

परंतु जेव्हा या “काहीतरी” चे व्हॉल्यूम शेकडो मेगाबाइट्समध्ये मोजू लागते आणि सेवेने डेटाबेससह 24x7 कार्य करणे सुरू ठेवले पाहिजे, तेव्हा बरेच दुष्परिणाम उद्भवतात ज्यामुळे तुमचे आयुष्य उद्ध्वस्त होईल.

PostgreSQL मध्ये (आणि फक्त त्यातच नाही) त्यांच्याशी व्यवहार करण्यासाठी, तुम्ही काही ऑप्टिमायझेशन वापरू शकता जे तुम्हाला प्रत्येक गोष्टीवर जलद आणि कमी संसाधनांच्या वापरासह प्रक्रिया करण्यास अनुमती देईल.

1. कुठे पाठवायचे?

प्रथम, आम्ही "प्रक्रिया" करू इच्छित असलेला डेटा कोठे अपलोड करू शकतो हे ठरवूया.

१.१. तात्पुरती सारणी (तात्पुरती सारणी)

तत्वतः, PostgreSQL साठी तात्पुरती सारण्या इतर कोणत्याही सारख्याच असतात. त्यामुळे अंधश्रद्धा आवडतात "तेथे सर्व काही फक्त मेमरीमध्ये साठवले जाते आणि ते संपू शकते". परंतु त्यात अनेक महत्त्वपूर्ण फरक देखील आहेत.

डेटाबेसमधील प्रत्येक कनेक्शनसाठी आपले स्वतःचे "नेमस्पेस"

एकाच वेळी दोन कनेक्शन जोडण्याचा प्रयत्न केल्यास CREATE TABLE x, मग कोणीतरी नक्कीच मिळेल विशिष्टता नसलेली त्रुटी डेटाबेस ऑब्जेक्ट्स.

पण दोघांनी अंमलात आणण्याचा प्रयत्न केला तर CREATE TEMPORARY TABLE x, मग दोघेही ते सामान्यपणे करतील आणि प्रत्येकाला मिळेल तुमची प्रत टेबल आणि त्यांच्यामध्ये काहीही साम्य असणार नाही.

डिस्कनेक्ट करताना "स्व-संहार".

जेव्हा कनेक्शन बंद होते, तेव्हा सर्व तात्पुरत्या टेबल्स आपोआप हटवल्या जातात, त्यामुळे व्यक्तिचलितपणे DROP TABLE x याशिवाय काहीच अर्थ नाही...

आपण माध्यमातून काम करत असल्यास व्यवहार मोडमध्ये pgbouncer, नंतर डेटाबेस अजूनही विश्वास ठेवतो की हे कनेक्शन अद्याप सक्रिय आहे आणि त्यात ही तात्पुरती सारणी अद्याप अस्तित्वात आहे.

म्हणून, pgbouncer ला वेगळ्या कनेक्शनवरून ते पुन्हा तयार करण्याचा प्रयत्न केल्यास त्रुटी येईल. परंतु हे वापरून टाळता येऊ शकते CREATE TEMPORARY TABLE IF NOT EXISTS x.

खरे आहे, तरीही असे न करणे चांगले आहे, कारण नंतर आपण "अचानक" तेथे "मागील मालक" कडील शिल्लक डेटा शोधू शकता. त्याऐवजी, मॅन्युअल वाचणे आणि टेबल तयार करताना ते जोडणे शक्य आहे हे पाहणे अधिक चांगले आहे ON COMMIT DROP - म्हणजे, व्यवहार पूर्ण झाल्यावर, टेबल आपोआप हटवले जाईल.

न-प्रतिकृती

कारण ते फक्त एका विशिष्ट कनेक्शनशी संबंधित आहेत, तात्पुरत्या टेबल्सची प्रतिकृती बनवली जात नाही. परंतु यामुळे डेटाच्या दुहेरी रेकॉर्डिंगची गरज नाहीशी होते heap + WAL मध्ये, त्यामुळे त्यात INSERT/UPDATE/DELETE करणे अधिक जलद आहे.

परंतु तात्पुरते टेबल अजूनही "जवळजवळ सामान्य" टेबल असल्याने, ते प्रतिकृतीवर देखील तयार केले जाऊ शकत नाही. किमान आत्ता तरी, जरी संबंधित पॅच बर्याच काळापासून फिरत आहे.

१.२. अनलॉग केलेले टेबल

परंतु तुम्ही काय करावे, उदाहरणार्थ, जर तुमच्याकडे काही प्रकारची अवजड ETL प्रक्रिया असेल जी एका व्यवहारात लागू केली जाऊ शकत नाही, परंतु तरीही तुमच्याकडे व्यवहार मोडमध्ये pgbouncer? ..

किंवा डेटा प्रवाह इतका मोठा आहे की एका कनेक्शनवर पुरेशी बँडविड्थ नाही डेटाबेसमधून (वाचा, प्रत्येक CPU एक प्रक्रिया)?..

किंवा काही ऑपरेशन्स चालू आहेत असिंक्रोनसपणे वेगवेगळ्या संबंधात?..

येथे एकच पर्याय आहे - तात्पुरते गैर-तात्पुरते टेबल तयार करा. पुन, होय. ते आहे:

• जास्तीत जास्त यादृच्छिक नावांसह "माझे स्वतःचे" टेबल तयार केले जेणेकरुन कोणाशीही एकमेकांना छेदू नये
• काढा: त्यांना बाह्य स्रोताकडील डेटाने भरले
• रुपांतर: रूपांतरित, की लिंकिंग फील्ड भरली
• लोड: लक्ष्य सारण्यांमध्ये तयार डेटा ओतला
• "माझे" टेबल हटवले

आणि आता - मलम मध्ये एक माशी. खरं तर, PostgreSQL मधील सर्व लेखन दोनदा होते - WAL मध्ये प्रथम, नंतर टेबल/इंडेक्स बॉडीमध्ये. हे सर्व ACID ला समर्थन देण्यासाठी आणि दरम्यान डेटा दृश्यमानता योग्य करण्यासाठी केले जाते COMMIT'नटी आणि ROLLBACK'शून्य व्यवहार.

पण आम्हाला याची गरज नाही! आमच्याकडे संपूर्ण प्रक्रिया आहे एकतर ते पूर्णपणे यशस्वी झाले किंवा ते नव्हते.. किती मध्यवर्ती व्यवहार होतील याने काही फरक पडत नाही - आम्हाला "मध्यातून प्रक्रिया सुरू ठेवण्यात" स्वारस्य नाही, विशेषत: जेव्हा ते कुठे होते हे स्पष्ट नसते.

हे करण्यासाठी, PostgreSQL विकसकांनी, आवृत्ती 9.1 मध्ये, अशी गोष्ट सादर केली. अनलॉग केलेले टेबल:

या संकेतासह, सारणी अनलॉग्ड म्हणून तयार केली जाते. अनलॉग टेबलवर लिहिलेला डेटा राइट-अहेड लॉगमधून जात नाही (धडा 29 पाहा), ज्यामुळे अशा टेबल्स नेहमीपेक्षा खूप वेगाने काम करा. तथापि, ते अपयशापासून मुक्त नाहीत; सर्व्हर अयशस्वी झाल्यास किंवा आपत्कालीन बंद झाल्यास, एक अनलॉग टेबल आपोआप कापले. याव्यतिरिक्त, अनलॉग टेबलची सामग्री प्रतिकृती नाही गुलाम सर्व्हर करण्यासाठी. अनलॉग टेबलवर तयार केलेली कोणतीही इंडेक्स आपोआप अनलॉग होतात.

थोडक्यात सांगायचे तर, ते खूप जलद होईल, परंतु डेटाबेस सर्व्हर "पडल्यास", ते अप्रिय असेल. परंतु हे किती वेळा घडते आणि डेटाबेसचे "पुनरुज्जीवन" केल्यानंतर "मध्यभागी" हे योग्यरित्या कसे दुरुस्त करायचे हे तुमच्या ETL प्रक्रियेला माहीत आहे का?...

नसल्यास, आणि वरील केस तुमच्यासारखेच आहे, वापरा UNLOGGED, पण कधीच नाही वास्तविक टेबलवर ही विशेषता सक्षम करू नका, ज्यामधून डेटा तुम्हाला प्रिय आहे.

१.३. कमिट करा { पंक्ती हटवा | थेंब}

ही रचना तुम्हाला टेबल तयार करताना व्यवहार पूर्ण झाल्यावर स्वयंचलित वर्तन निर्दिष्ट करण्याची परवानगी देते.

वर ON COMMIT DROP मी आधीच वर लिहिले आहे, ते व्युत्पन्न करते DROP TABLE, पण सह ON COMMIT DELETE ROWS परिस्थिती अधिक मनोरंजक आहे - ती येथे व्युत्पन्न झाली आहे TRUNCATE TABLE.

तात्पुरत्या सारणीचे मेटा-वर्णन संचयित करण्यासाठी संपूर्ण पायाभूत सुविधा नियमित सारणीप्रमाणेच असते, नंतर तात्पुरत्या टेबल्सची सतत निर्मिती आणि हटवण्यामुळे सिस्टम टेबल्सची गंभीर "सूज" होते pg_class, pg_attribute, pg_attrdef, pg_depend,…

आता कल्पना करा की तुमच्याकडे डेटाबेसशी थेट कनेक्शनवर एक कार्यकर्ता आहे, जो प्रत्येक सेकंदाला एक नवीन व्यवहार उघडतो, तात्पुरता टेबल तयार करतो, भरतो, प्रक्रिया करतो आणि हटवतो... सिस्टम टेबलमध्ये जास्त कचरा जमा होईल आणि यामुळे प्रत्येक ऑपरेशनसाठी अतिरिक्त ब्रेक लागतील.

सर्वसाधारणपणे, हे करू नका! या प्रकरणात ते अधिक प्रभावी आहे CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ते व्यवहाराच्या चक्रातून बाहेर काढा - मग प्रत्येक नवीन व्यवहाराच्या सुरूवातीस टेबल आधीच आहेत अस्तित्वात असेल (एक कॉल सेव्ह करा CREATE), परंतु रिकामे असेल, ना धन्यवाद TRUNCATE (आम्ही त्याचा कॉल देखील सेव्ह केला) मागील व्यवहार पूर्ण करताना.

१.४. लाइक...यासह...

मी सुरुवातीला नमूद केले आहे की तात्पुरत्या सारण्यांसाठी वापरल्या जाणाऱ्या सामान्य प्रकरणांपैकी एक म्हणजे विविध प्रकारच्या आयात - आणि विकसक थकल्यासारखे त्याच्या तात्पुरत्या घोषणेमध्ये लक्ष्य सारणीच्या फील्डची सूची कॉपी-पेस्ट करतो...

पण आळस हे प्रगतीचे इंजिन आहे! म्हणून "नमुन्यावर आधारित" एक नवीन सारणी तयार करा हे बरेच सोपे असू शकते:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

त्यानंतर तुम्ही या टेबलमध्ये भरपूर डेटा व्युत्पन्न करू शकत असल्याने, त्याद्वारे शोधणे कधीही जलद होणार नाही. पण यावर एक पारंपारिक उपाय आहे - निर्देशांक! आणि हो, तात्पुरत्या टेबलमध्ये अनुक्रमणिका देखील असू शकतात.

बहुतेकदा, आवश्यक निर्देशांक लक्ष्य सारणीच्या निर्देशांकांशी जुळतात, आपण फक्त लिहू शकता LIKE target_table INCLUDING INDEXES.

जर तुम्हालाही गरज असेल DEFAULT-values ​​(उदाहरणार्थ, प्राथमिक की मूल्ये भरण्यासाठी), तुम्ही वापरू शकता LIKE target_table INCLUDING DEFAULTS. किंवा फक्त - LIKE target_table INCLUDING ALL — कॉपी डीफॉल्ट, अनुक्रमणिका, मर्यादा,...

परंतु येथे आपल्याला हे समजून घेणे आवश्यक आहे की आपण तयार केले तर अनुक्रमणिकेसह ताबडतोब सारणी आयात करा, नंतर डेटा लोड होण्यास जास्त वेळ लागेलआपण प्रथम सर्वकाही भरल्यास, आणि त्यानंतरच निर्देशांक रोल अप केले तर - उदाहरण म्हणून हे कसे करते ते पहा pg_dump.

सर्वसाधारणपणे आरटीएफएम!

2. कसे लिहायचे?

मला फक्त म्हणू द्या - वापरा COPY- "पॅक" ऐवजी प्रवाह INSERT, काही वेळा प्रवेग. आपण थेट पूर्व-व्युत्पन्न केलेल्या फाईलमधून देखील करू शकता.

3. प्रक्रिया कशी करावी?

चला तर मग, आपला परिचय असा काहीसा पाहू या:

• तुमच्या डेटाबेसमध्ये क्लायंटच्या डेटासह एक टेबल आहे 1M रेकॉर्ड
• दररोज एक क्लायंट तुम्हाला नवीन पाठवतो पूर्ण "प्रतिमा"
• अनुभवावरून तुम्हाला ते वेळोवेळी कळते 10K पेक्षा जास्त रेकॉर्ड बदलले जात नाहीत

अशा परिस्थितीचे उत्कृष्ट उदाहरण आहे KLADR बेस — एकूण बरेच पत्ते आहेत, परंतु प्रत्येक साप्ताहिक अपलोडमध्ये अगदी राष्ट्रीय स्तरावर फारच कमी बदल (वस्तीचे नाव बदलणे, रस्त्यांचे एकत्रीकरण, नवीन घरे दिसणे) आहेत.

३.१. पूर्ण सिंक्रोनाइझेशन अल्गोरिदम

साधेपणासाठी, आपण असे म्हणू की आपल्याला डेटाची पुनर्रचना करण्याची देखील आवश्यकता नाही - फक्त टेबलला इच्छित फॉर्ममध्ये आणा, म्हणजे:

• काढा सर्वकाही जे यापुढे अस्तित्वात नाही
• अद्यतनित करा जे आधीपासून अस्तित्वात आहे आणि अपडेट करणे आवश्यक आहे
• घाला सर्व काही जे अद्याप झाले नाही

या क्रमाने ऑपरेशन्स का केल्या पाहिजेत? कारण अशा प्रकारे टेबलचा आकार कमीत कमी वाढेल (MVCC लक्षात ठेवा!).

dst मधून हटवा

नाही, अर्थातच तुम्ही फक्त दोन ऑपरेशन्स करून मिळवू शकता:

• काढा (DELETE) सर्वसाधारणपणे सर्वकाही
• घाला सर्व नवीन प्रतिमेतून

पण त्याच वेळी, MVCC चे आभार, टेबलचा आकार नक्की दुप्पट वाढेल! 1K अद्यतनामुळे सारणीमध्ये रेकॉर्डच्या +10M प्रतिमा मिळवणे खूप रिडंडंसी आहे...

TRUNCATE dst

अधिक अनुभवी विकसकाला माहित आहे की संपूर्ण टॅब्लेट स्वस्तात साफ केला जाऊ शकतो:

• स्पष्ट (TRUNCATE) संपूर्ण टेबल
• घाला सर्व नवीन प्रतिमेतून

पद्धत प्रभावी आहे, कधी कधी जोरदार लागू, पण एक समस्या आहे... आम्ही 1M रेकॉर्ड बर्याच काळासाठी जोडत आहोत, त्यामुळे आम्हाला टेबल रिकामे ठेवता येणार नाही (जसे ते एकाच व्यवहारात गुंडाळल्याशिवाय होईल).

ज्याचा अर्थ होतो:

• आम्ही सुरू करत आहोत दीर्घकाळ चालणारा व्यवहार
• TRUNCATE लादतो अनन्य प्रवेश- अवरोधित करणे
• आम्ही बर्याच काळासाठी आणि इतर प्रत्येकजण या वेळी अंतर्भूत करतो करू शकत नाही SELECT

काही ठीक होत नाहीये...

सारणी बदला... पुनर्नामित करा... / टेबल ड्रॉप करा...

एक पर्याय म्हणजे प्रत्येक गोष्ट वेगळ्या नवीन टेबलमध्ये भरणे, आणि नंतर जुन्याच्या जागी त्याचे नाव बदलणे. काही ओंगळ लहान गोष्टी:

• अजूनही खूप अनन्य प्रवेश, जरी लक्षणीय कमी वेळ
• या सारणीसाठी सर्व क्वेरी योजना/आकडेवारी रीसेट केल्या आहेत, ANALYZE चालवणे आवश्यक आहे
• सर्व परदेशी कळा तुटलेल्या आहेत (एफके) टेबलवर

सायमन रिग्सचा एक WIP पॅच होता ज्याने बनवण्याचा सल्ला दिला होता ALTER-संख्या आणि FK ला स्पर्श न करता, फाइल स्तरावर टेबल बॉडी बदलण्याचे ऑपरेशन, परंतु कोरम गोळा केला नाही.

हटवा, अपडेट करा, घाला

तर, आम्ही तीन ऑपरेशन्सच्या नॉन-ब्लॉकिंग पर्यायावर सेटल करतो. जवळजवळ तीन... हे सर्वात प्रभावीपणे कसे करायचे?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

३.२. पोस्ट-प्रोसेसिंग आयात करा

त्याच KLADR मध्ये, सर्व बदललेले रेकॉर्ड पोस्ट-प्रोसेसिंगद्वारे अतिरिक्तपणे चालवले जाणे आवश्यक आहे - सामान्यीकृत, कीवर्ड हायलाइट केले गेले आणि आवश्यक संरचनांमध्ये कमी केले गेले. पण तुला कसं कळणार - नक्की काय बदललेसिंक्रोनाइझेशन कोड गुंतागुंत न करता, आदर्शपणे त्यास स्पर्श न करता?

सिंक्रोनाइझेशनच्या वेळी फक्त तुमच्या प्रक्रियेत लेखन प्रवेश असेल, तर तुम्ही ट्रिगर वापरू शकता जे आमच्यासाठी सर्व बदल एकत्रित करेल:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

आता आम्ही सिंक्रोनाइझेशन सुरू करण्यापूर्वी ट्रिगर लागू करू शकतो (किंवा त्यांना द्वारे सक्षम करू ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

आणि मग आम्ही लॉग टेबलमधून आम्हाला आवश्यक असलेले सर्व बदल शांतपणे काढतो आणि ते अतिरिक्त हँडलर्सद्वारे चालवतो.

३.३. लिंक केलेले संच आयात करत आहे

वर आम्ही अशा प्रकरणांचा विचार केला आहे जेव्हा स्त्रोत आणि गंतव्यस्थानाची डेटा संरचना समान असतात. पण जर बाह्य प्रणालीवरील अपलोडचे स्वरूप आमच्या डेटाबेसमधील स्टोरेज स्ट्रक्चरपेक्षा वेगळे असेल तर?

क्लायंटचे स्टोरेज आणि त्यांच्या खात्यांचे उदाहरण घेऊ, क्लासिक "अनेक-टू-वन" पर्याय:

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

परंतु बाह्य स्त्रोताकडील डाउनलोड आमच्याकडे “ऑल इन वन” स्वरूपात येते:

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

अर्थात, या आवृत्तीमध्ये ग्राहक डेटा डुप्लिकेट केला जाऊ शकतो आणि मुख्य रेकॉर्ड "खाते" आहे:

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

मॉडेलसाठी, आम्ही आमचा चाचणी डेटा टाकू, परंतु लक्षात ठेवा - COPY अधिक कार्यक्षम!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

प्रथम, आपण त्या “कट” ठळक करू या ज्याचा आपला “तथ्य” संदर्भित करतो. आमच्या बाबतीत, पावत्या ग्राहकांचा संदर्भ घेतात:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

ग्राहक आयडी सह खाती योग्यरित्या संबद्ध करण्यासाठी, आम्हाला प्रथम हे अभिज्ञापक शोधणे किंवा निर्माण करणे आवश्यक आहे. चला त्यांच्या खाली फील्ड जोडूया:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

वर वर्णन केलेली टेबल सिंक्रोनाइझेशन पद्धत एका छोट्या दुरुस्तीसह वापरूया - आम्ही लक्ष्य सारणीमधील काहीही अद्यतनित किंवा हटविणार नाही, कारण आम्ही क्लायंट "फक्त जोडण्यासाठी" आयात करतो:

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

खरं तर, सर्वकाही आत आहे invoice_import आता आमच्याकडे संपर्क फील्ड भरले आहे client_id, ज्यासह आम्ही बीजक समाविष्ट करू.

स्त्रोत: www.habr.com