2

Using Postgres 10.5, given the following:

postgres=# begin; 
   with updated as (update bippy set id = 100 where id = 1 returning 1)
select id, ts from bippy;
commit;
BEGIN
 id |              ts               
----+-------------------------------
 66 | 2019-07-09 10:42:32.062496-04
 80 | 2019-07-09 10:43:28.068512-04
 80 | 2019-07-09 10:43:28.596341-04
 80 | 2019-07-15 14:42:32.062496-04
  1 | 2019-07-09 10:42:23.142809-04
  1 | 2019-07-09 10:42:25.366664-04
  1 | 2019-07-09 10:42:26.142027-04
  1 | 2019-07-09 10:42:26.702398-04
(8 rows)

COMMIT

Why does the above return an id of 1 instead of 100, i.e. per the Common Table Expression?

When I select from the table immediately after, I see that the updates have taken place.

postgres=# select * from bippy;
              ts               | id  
-------------------------------+-----
 2019-07-09 10:42:32.062496-04 |  66
 2019-07-09 10:43:28.068512-04 |  80
 2019-07-09 10:43:28.596341-04 |  80
 2019-07-15 14:42:32.062496-04 |  80
 2019-07-09 10:42:23.142809-04 | 100
 2019-07-09 10:42:25.366664-04 | 100
 2019-07-09 10:42:26.142027-04 | 100
 2019-07-09 10:42:26.702398-04 | 100
(8 rows)
  • 1
    returning 1 says to return the value 1 from the CTE. It's the same as select 1. But why the CTE to begin with? update bippy set id = 100 where id = 1 returning id, ts will do what you want – a_horse_with_no_name Sep 6 '19 at 16:26
2

You need to query the CTE and return * to see the changed data.

   with updated as (update bippy set id = 100 where id = 1 returning *)
   select id, ts from updated;

Or if you want all rows returned (updated and non-updated):

   with updated as (update bippy set id = 100 where id = 1 returning id, ts)
   select id, ts from updated
   UNION ALL
   select id, ts from bippy where id <> 1;

From the documentation:

7.8.2. Data-Modifying Statements in WITH

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. An example of this is that in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of the UPDATE, while in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.