Need row returned from upsert

Question

I have a table that I need to upsert. If the row already exists then I want to update and return the row. If the row doesn't already exist then I need to insert and return the row. With the query I have below I get the row returned on insert, but not on update.

Table "main.message_account_seen"
    Column      |           Type           |                             Modifiers                             
----------------+--------------------------+-------------------------------------------------------------------
id              | integer                  | not null default nextval('message_account_seen_id_seq'::regclass)
field_config_id | integer                  | not null
edit_stamp      | timestamp with time zone | not null default now()
audit_stamp     | timestamp with time zone | 
message_id      | integer                  | not null
account_id      | integer                  | 

Here's the sql.

with upsert as (
    update message_account_seen set (message_id, account_id, field_config_id ) = (1, 60, 980) 
    where message_id = 1 and account_id = 60 and field_config_id = 980 returning *
)
insert into message_account_seen (message_id, account_id, field_config_id)
select 1, 60, 980
where not exists (select message_id, account_id, field_config_id from upsert) returning *;

I can't do a postgres function, it needs to be handled in a regular sql query. Also, there is no constraint on the table for uniqueness of row otherwise I would use on conflict. But I'm willing to scrap this query and go with something else if need be.

These are the results when I run the query, and then run it again. You can see that on the insert or first run I get the row returned. However on subsequent runs of the query I get 0 rows returned. I know that it's working because the edit_stamp increases in time. That's a good thing.

# with upsert as (
    update message_account_seen set (message_id, account_id, field_config_id ) = (1, 60, 980) 
    where message_id = 1 and account_id = 60 and field_config_id = 980 returning *
)
insert into message_account_seen (message_id, account_id, field_config_id)
select 1, 60, 980
where not exists (select message_id, account_id, field_config_id from upsert) returning *;
id | field_config_id |           edit_stamp           | audit_stamp | message_id | account_id 
 --+-----------------+--------------------------------+-------------+------------+------------
38 |             980 | 09/27/2016 11:43:22.153908 MDT |             |          1 |         60
(1 row)

INSERT 0 1
# with upsert as (
    update message_account_seen set (message_id, account_id, field_config_id ) = (1, 60, 980) 
    where message_id = 1 and account_id = 60 and field_config_id = 980 returning *
)
insert into message_account_seen (message_id, account_id, field_config_id)
select 1, 60, 980
where not exists (select message_id, account_id, field_config_id from upsert) returning *;
id | field_config_id | edit_stamp | audit_stamp | message_id | account_id 
----+-----------------+------------+-------------+------------+------------
(0 rows)

INSERT 0 0

Answer 1

When the update succeeds its result is not returned in your query. This does it:

with upsert as (
    update message_account_seen
    set (message_id, account_id, field_config_id ) = (1, 60, 980) 
    where (message_id, account_id, field_config_id) = (1, 60, 980)
    returning *
), ins as (
    insert into message_account_seen (message_id, account_id, field_config_id)
    select 1, 60, 980
    where not exists (select 1 from upsert)
    returning *
)
select * from upsert
union all
select * from ins
;