Upsert into table 'a' and select with the returned Id the rows of table 'b' (for-CodePudding

I try to insert a row with an UPSERT. Then I always want to get back the id of that row (does not matter if already exists or newly created).

And finally I search for that id in another table. Why it is so complicated? I already tried UNION ALL and subqueries but it did not help.

 WITH w AS(
   INSERT INTO "week" (userid,weeknumber,yearnumber,state) 
   VALUES ($1,$2,$3,$4) 
   ON CONFLICT(userid,weeknumber,yearnumber) 
     DO NOTHING
   RETURNING id
)
SELECT cs.id,cs.weekid
FROM "constructionsite" cs
WHERE cs.weekid = w.id

CodePudding user response：

You do not get anything returned for an existing row the on conflict happens - it does not return the is. But you have an the columns that generated the duplicate error. Use that to get the is for your select.

with w as 
     (
       insert into "week" (userid,weeknumber,yearnumber,state) 
           values ($1,$2,$3,$4) 
           on conflict(userid,weeknumber,yearnumber) 
           do nothing
           returning id
     )
select cs.id,cs.weekid
  from "constructionsite" cs 
 where cs.weekid = (select  wk.id
                      from "week" wk         
                     where wk.userid     = $1
                       and wk.weeknumber = $2
                       and wk.yearnumber = $3
                   );

Depending on your app language you may need to adjust (or duplicate) the parameter valies.

CodePudding user response：

You miss w table in from. Try this one

WITH w AS(
   INSERT INTO "week" (userid,weeknumber,yearnumber,state) 
   VALUES ($1,$2,$3,$4) 
   ON CONFLICT(userid,weeknumber,yearnumber) 
     DO NOTHING
   RETURNING id
)
SELECT cs.id,cs.weekid
FROM "constructionsite" cs,w
WHERE cs.weekid = w.id