Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example).

And I'm just not a psql expert at all to find out immediately, whether these solutions are old because they are well recommended or they are (well, almost all of them are) just toy examples not appropriate to production use.

What is the most thread-safe way to implement UPSERT in PostgreSQL?

UPDATE (2015-08-20):

There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories.

Here is an example, assuming item_id is your Primary Key:

INSERT INTO my_table
    (item_id, price)
VALUES
    (123456, 10.99)
ON
    CONFLICT (item_id)
DO UPDATE SET
    price = EXCLUDED.price

Original Post...

Here is an implementation I arrived upon when desiring to gain visibility into whether an insert or update occurred.

The definition of upsert_data is to consolidate the values into a single resource, rather than having to specify the price and item_id twice: Once for the update, again for the insert.

WITH upsert_data AS (
    SELECT
    '19.99'::numeric(10,2) AS price,
    'abcdefg'::character varying AS item_id
),
update_outcome AS (
    UPDATE pricing_tbl
    SET price = upsert_data.price
    FROM upsert_data
    WHERE pricing_tbl.item_id = upsert_data.item_id
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        upsert_data.price AS price,
        upsert_data.item_id AS item_id
    FROM upsert_data
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome

If you don't like the use of upsert_data, here is an alternate implementation:

WITH update_outcome AS (
    UPDATE pricing_tbl
    SET price = '19.99'
    WHERE pricing_tbl.item_id = 'abcdefg'
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        '19.99' AS price,
        'abcdefg' AS item_id
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome

StackExchange Database Administrators Q#13468, answer score: 29