How to update value in inserted row in PostgreSQL?

Question

I have a table t1

create table t1 (
  id int primary key not null,
  name varchar(64),
  str2 varchar(64)
);

I want to get the row ID and update a field str2 in this row.

I tried this:

WITH inserted AS (
  insert into t1(id, name) values (38, 'www') returning *
) UPDATE t1 SET str2='aaa' WHERE id IN (SELECT id FROM inserted);
select * from t1;

I expected:

| id | name |   str2 |
|----|------|--------|
| 38 |  www |   aaa  |

But the actual result was:

| id | name |   str2 |
|----|------|--------|
| 38 |  www | (null) |

See this SQLFiddle for a reproduction scenario.

What am I doing wrong?

Answer 1

There's no point in using UPDATE to rows INSERTed in the same statement, as the INSERT could set the missing column in the first place:

insert into t1(id, name, str2) values (38, 'www', 'aaa');

Besides the pointlessness, it doesn't work, as shown in the question.

The reason is mentioned in the doc in 7.8.2. Data-Modifying Statements in WITH:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables

[...]

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.