Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 08/28/2014 04:43 AM, Peter Geoghegan wrote:
> -- Nesting within wCTE:
> WITH t AS (
> INSERT INTO z SELECT i, 'insert'
> FROM generate_series(0, 16) i
> ON CONFLICT UPDATE SET v = v || 'update' -- use of
> operators/functions in targetlist
> RETURNING * -- only projects inserted tuples, never updated tuples
> )
> SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;

Personally I would find it surprising if RETURNING did not also return
the updated tuples. In many use cases for upsert the user does not care
if the row was new or not.

What I think would be useful is if all tuples were returned but there
was some way to filter out only the inserted ones.

--
Andreas Karlsson