Consecutive Selects cause unexpected token

[mrflip]

This query gives Error: Unexpected token at 1:1324:

with "createIfCTE" as (insert into "loc" ("branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz") select 'building' AS "branch", '0110ve2haxt00kst0ckc0deftw'::varchar AS "crTC", 'dpt.ello:main_dpt'::varchar AS "deptID", json_build_object()::jsonb AS "details", 'ello_tower'::varchar AS "handle", 'loc.ello:ello_tower'::varchar AS "id", 'ello_tower'::varchar AS "idkey", 'ima.pub:letter_t'::varchar AS "image0ID", NULL::varchar AS "note0", 'ello'::varchar AS "okey", 'ctp.ello:main_ctp'::varchar AS "shipToID", 'loc.ello:ello_tower'::varchar AS "srcID", 'Tower'::varchar AS "title", '01f001th1s1saser10vsj0vrny'::varchar AS "tkvTC", 'Loc'::varchar AS "typename", '0110ve2haxt00kst0ckc0deftw'::varchar AS "upTC", 'normal' AS "viz" where not exists (select 1 as YO from "loc" where "okey" = 'ello' and "typename" = 'Loc' and "branch" = 'site') on conflict ("id") do update set "deptID" = excluded."deptID", "image0ID" = excluded."image0ID", "note0" = excluded."note0", "shipToID" = excluded."shipToID", "title" = excluded."title", "viz" = excluded."viz" returning "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz") (select "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz" from "createIfCTE") union (select "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz" from "loc" where "okey" = 'ello' and "typename" = 'Loc' and "branch" = 'site' and not (not exists (select 1 as YO from "loc" where "okey" = 'ello' and "typename" = 'Loc' and "branch" = 'site')) order by "id" desc limit 1)

As prettified by VSCode:

with
  "createIfCTE" as (
    insert into
      "loc" (
        "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz"
      )
    select
      'building' AS "branch",
      '0110ve2haxt00kst0ckc0deftw' ::varchar AS "crTC",
      'dpt.ello:main_dpt' ::varchar AS "deptID",
      json_build_object() ::jsonb AS "details",
      'ello_tower' ::varchar AS "handle",
      'loc.ello:ello_tower' ::varchar AS "id",
      'ello_tower' ::varchar AS "idkey",
      'ima.pub:letter_t' ::varchar AS "image0ID",
      NULL ::varchar AS "note0",
      'ello' ::varchar AS "okey",
      'ctp.ello:main_ctp' ::varchar AS "shipToID",
      'loc.ello:ello_tower' ::varchar AS "srcID",
      'Tower' ::varchar AS "title",
      '01f001th1s1saser10vsj0vrny' ::varchar AS "tkvTC",
      'Loc' ::varchar AS "typename",
      '0110ve2haxt00kst0ckc0deftw' ::varchar AS "upTC",
      'normal' AS "viz"
    where
      not exists (
        select
          1 as YO
        from
          "loc"
        where
          "okey" = 'ello'
          and "typename" = 'Loc'
          and "branch" = 'site'
      ) on conflict ("id")
    do
    update
    set
      "deptID" = excluded."deptID", "image0ID" = excluded."image0ID", "note0" = excluded."note0", "shipToID" = excluded."shipToID", "title" = excluded."title", "viz" = excluded."viz"
   returning "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz"
  ) (
-- the error is here ^^^
    select
      "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz"
    from
      "createIfCTE"
  )
union
(
  select
    "branch", "crTC", "deptID", "details", "handle", "id", "idkey", "image0ID", "note0", "okey", "shipToID", "srcID", "title", "tkvTC", "typename", "upTC", "viz"
  from
    "loc"
  where
    "okey" = 'ello'
    and "typename" = 'Loc'
    and "branch" = 'site'
    and not (
      not exists (
        select
          1 as YO
        from
          "loc"
        where
          "okey" = 'ello'
          and "typename" = 'Loc'
          and "branch" = 'site'
      )
    )
  order by
    "id" desc
  limit
    1
)

[ivank]

Huh I guess it does not expect wrapped select (with brackets). Just queries separated by ,;

Man you're really putting this tool through its paces! I love it :)