There is a tree-structures in two-tables with ParentId field. How to make UPSERT (INSERT ... ON CONFLICT (...) DO UPDATE ...) between them? The problem is that parent record for the current record in the 1st table may not be yet synchronized with 2nd table (missed in 2nd table).

This is query I am trying to write:

WITH upsert AS (
    d."Oid" "Parent",
  FROM "KsuNsiDepartment" c
  LEFT JOIN "KsuNsiDepartment" p on c."ParentDepartment" = p."Oid"
  LEFT JOIN "Department" d on p."ExternalId" :: varchar(100) = d."ExternalCode"
    "GCRecord" IS NULL
    /*AND OID IN (...)*/
INSERT INTO "Department" ("Name", "Parent", "ExternalCode")
    upsert."ExternalId" :: varchar(100)
  ) ON CONFLICT ("ExternalCode") DO
  "Name" = EXCLUDED."Name",
  "Parent" = EXCLUDED."Parent";

The problem that for current record from KsuNsiDepartment parent record from KsuNsiDepartment may exists, but may be not yet corresponding record for this parent record in the Department table

  • 1
    It would be nice if you could provide sample data and your desired result - maybe in the form of a fiddle. There are some articles about how to answer questions here on my profile - you might like to take a look? p.s. welcome to the forum! :-) – Vérace Nov 1 '19 at 8:33
  • What is the data model? Is the depth of the tree only 2, like your query suggests? – Laurenz Albe Nov 1 '19 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.