Order of Execution of Set clause in Update Query (Postgres)

Question

I recently came across this strange behaviour in Postgres. I had a table like the following one:

sasdb=# \d emp_manager_rel 
           Table "db3004db.emp_manager_rel"
   Column   |  Type  | Collation | Nullable | Default 
------------+--------+-----------+----------+---------
 emp_id     | bigint |           |          | 
 manager_id | bigint |           |          | 

select * from emp_manager_rel ;
 emp_id | manager_id 
--------+------------
      1 |        123

I executed the following update statement :

UPDATE 1:

update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;

which update the table like follow:

 emp_id | manager_id 
--------+------------
      4 |        124

UPDATE 2: I executed the following query (on the original table, not on the updated)

update emp_manager_rel set  emp_id=emp_id*4 , manager_id = manager_id+emp_id ;

it updates the table like follows:

 emp_id | manager_id 
--------+------------
      4 |        124

I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.

Answer 1

There is nothing "strange" about this behaviour - it's the only sane way to process such an update.

There is no such thing as "order of updates" - all SET clauses should be assumed to happen in parallel.

The SQL standard requires that the right hand side of the assignment is the value of the columns as it was before the UPDATE statement started. The order in which the column assignments are listed in the UPDATE statement is irrelevant for the outcome of the UPDATE statement.

Any other behaviour of the UPDATE statement would be a bug and a violation of the SQL standard.

For the same reason, the following statement will swap the two column values:

update some_table
   set x = y, y = x;

or

update some_table
   set y = x, x = y;