3

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.

7

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;
  • this is regarding alter statements, is there anything like "order" in executing alter statement. We have alter statements like drop col1,drop col2,add col1 in same stament. I am working on a project which involves creating DB like layer in java. So I need how SQL behaves in this scenario and have to pretend the same. Any thoughts regarding alter execution – Dinesh Kumar Aug 27 '18 at 16:17
  • 2
    @DineshKumar SQL statements are designed to be atomic. Just make multiple statements. – Brandon Aug 27 '18 at 17:35
  • 1
    @DineshKumar: I have no idea what you mean with the "order" when executing ALTER statements. You should ask a new question. But remember: DDL is transactional in Postgres, even if you use multiple statements you can make them appear as a single one to other sessions if you put everything into a single transaction. – a_horse_with_no_name Aug 27 '18 at 21:33
  • @a_horse_with_no_name sure will post a new question with better use case reg. this scenario. – Dinesh Kumar Aug 28 '18 at 0:58

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.