How to return values from "upsert" query in case when UPDATE has been performed? 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\".
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