单表更新

teledb=# update teledb_serial set nickname = 'random value' where id = 2;
UPDATE 1
teledb=# select * from teledb_serial;                                                                  
 id |   nickname  
----+---------------
  1 | hello teledb
  2 | random value
(2 rows)

null 条件的表达方法。

teledb=# insert into teledb_serial (id) values(3);
INSERT 0 1
teledb=# select * from teledb_serial;
 id |   nickname  
----+---------------
  1 | hello teledb
  2 | random value
  3 | 
(3 rows)

teledb=# update teledb_serial set nickname = 'random value' where nickname is null;;
UPDATE 1
teledb=# select * from teledb_serial;
 id |   nickname  
----+---------------
  1 | hello teledb
  2 | random value
  3 | random value
(3 rows)

多表关联更新

teledb=# update teledb_serial set nickname = 'updatefrom' from t_update where t_update.id = teledb_serial.id;
UPDATE 1
teledb=# select * from teledb_serial;
 id |   nickname   
----+--------------
  2 | random value
  1 | updatefrom
  3 | random value
(3 rows)

返回更新的数据

teledb=# update teledb_serial set nickname = 'returning' where id = (random()*2)::integer returning *;
 id | nickname  
----+-----------
  1 | returning
(1 row)

上面的语句随机更新了一些数据，然后返回更新过的记录，returning 机制旨在降低应用的复杂度。

多列匹配更新

teledb=# alter table t_update add column age int;
ALTER TABLE
teledb=# update t_update set (age , name) = ((random()*2)::integer, 'multi_column');
UPDATE 1
teledb=# select * from t_update ;
 id |     name     | age 
----+--------------+-----
  1 | multi_column |   0
(1 row)

shard key禁止更新操作

teledb=# update t_update set id = 2 where id =1;
ERROR:  Distributed column or partition column "id" can't be updated in current version