1
WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*)
INSERT INTO tbl (a)
SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
RETURNING *

This "upsert" statement works however I would like to retrieve either UPDATE or INSERTED values. When upsert performs INSERT (row is not yet in db) then inserted values are returned correctly returned from query.

However when UPDATE is performed then no values are returned (but row IS updated). I've tried adding RETURNING upsert.* but it yelds error missing FROM-clause entry for table \"upsert\".

5

You need to use a second CTE for the INSERT:

WITH upsert AS (
  UPDATE tbl SET a = 2 
  WHERE a = 1 
  RETURNING tbl.*
), inserted AS (
  INSERT INTO tbl (a)
  SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
  RETURNING *
)
select *
from upsert
union all
select * 
from inserted

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.