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