title: How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
body: <p>Writable CTEs were considered a solution to UPSERT prior to 9.5 as described in <a href="https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291">Insert, on duplicate update in PostgreSQL?</a></p>
  
  <p>It is possible to perform an UPSERT with the information whether it ended up as an UPDATE or an INSERT with the following Writable CTEs idiom:</p>
  
  <pre><code>WITH
      update_cte AS (
          UPDATE t SET v = $1 WHERE id = $2 RETURNING 'updated'::text status
      ),
      insert_cte AS (
          INSERT INTO t(id, v) SELECT $2, $1 WHERE NOT EXISTS
              (SELECT 1 FROM update_cte) RETURNING 'inserted'::text status
      )
   (SELECT status FROM update_cte) UNION (SELECT status FROM insert_cte)
  </code></pre>
  
  <p>This query will return either "updated" or "inserted", or may (rarely) fail with a constraint violation in as described in <a href="https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates">https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates</a></p>
  
  <p>Can something similar be achieved using PostgreSQL 9.5+ new "UPSERT" syntax, benefiting from its optimization and avoiding the possible constraint violation?</p>