CODEWITHSUNDEEP
oracle-databasepostgresqlmergeupsert
kobey
kobey

Reputation: 174

UPSERT: use source column that's not in excluded table

I'm trying to convert an oracle Merge statement to postgreSQL(>9.5). Unfortunately postgres(=<11) still doesn't support MERGE and the best alternative method I found was the INSERT ... ON CONFLICT also called UPSERT.

I was able to convert the more basic merge statement into upserts but for more complex queries I need to access columns from the source query the are not inserted but only used for logic in the DO UPDATE. Unfortunately the special excluded.-table uses the same column identifiers as the target.

ORACLE MERGE version 

MERGE INTO tgt
  USING (SELECT nam.text AS normalizedtext, norm.originaltext,
           norm.entryid AS entryID,
           nam.name_id AS name_id,
           norm.islink AS islink
         FROM norm, nam
         WHERE norm.name_id = nam.name_id
         GROUP BY originaltext
        ) source
  ON (tgt.languagecode = :langCode AND tgt.text = source.originaltext)
  WHEN MATCHED AND ptid IS NULL THEN
    UPDATE SET tgt.name_id = source.name_id,
               tgt.PTID = source.entryID,
               tgt.normname = CASE WHEN source.islink = 1 THEN 
               source.originaltext ELSE source.normalizedtext END CASE

Current postgress version 

INSERT INTO tgt (text, normname, ptid, name_id, languagecode)
    SELECT nam.text AS normalizedtext, norm.originaltext,
           norm.entryid AS entryID,
           nam.name_id AS name_id,
           :langCode,           -- edited
           norm.islink AS islink
         FROM norm, nam
         WHERE norm.name_id = nam.name_id
         GROUP BY originaltext
ON CONFLICT (text, languagecode)
DO UPDATE
    SET name_id = excluded.name_id,
           ptid = excluded.ptid,
           normname = CASE WHEN excluded.islink = 1 THEN          -- PROBLEM
           excluded.originaltext ELSE excluded.normalizedtext END -- PROBLEM
    WHERE ptid IS NULL;

The problem is that the excluded table does not contain the columns: isLink , originaltext and normalizedtext.

ERROR: column excluded.islink does not exist

Is there any workaround or how can u use the source columns in the update query? If this is not possible, is there an other alternative to write merge statements in postgres?

Upvotes: 2

Views: 750

Answers (1)

user330315
user330315

Reputation:

You can move that CASE expression to the SELECT statement:

INSERT INTO tgt (text, normname, ptid, name_id, languagecode)
SELECT nam.text, 
       case islink 
         when 1 then norm.originaltext
         else normalizedtext
       end as normname,
       norm.entryid AS ptid,
       nam.name_id AS name_id,
       :langCode as languagecode
FROM norm
  JOIN nam ON norm.name_id = nam.name_id
GROUP BY originaltext
ON CONFLICT (text, languagecode)
DO UPDATE
    SET name_id = excluded.name_id,
           ptid = excluded.ptid,
           normname = excluded.normname
    WHERE ptid IS NULL;

Upvotes: 1

Related Questions