postgresql 数据库 update 语句的初步使用

os: centos 7.4
db: postgresql 10.10

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Mon Feb  3 10:23:33 CST 2020 on pts/0
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

准备数据

postgres=# create table t0 ( c0 varchar(100),c1 varchar(100),c2 varchar(100));
insert into t0 ( c0,c1,c2) values ('1','1','1'),('2','2','2'),('3','3','3'),('4','4','4');
select * from t0;

 c0 | c1 | c2 
----+----+----
 1  | 1  | 1
 2  | 2  | 2
 3  | 3  | 3
 4  | 4  | 4
(4 rows)

postgres=# create table t1 as select * from t0;
delete from t1 where c0='3' or c0='4';
select * from t1;

 c0 | c1 | c2 
----+----+----
 1  | 1  | 1
 2  | 2  | 2
(2 rows)

postgresql 的 update 语法如下

postgres=# \h update
Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

update 方式一

postgres=# begin;
BEGIN
postgres=# 
postgres=# update t0 set c2=c2||c2 where c0='4';
UPDATE 1
postgres=# select * from t0;
 c0 | c1 | c2 
----+----+----
 1  | 1  | 1
 2  | 2  | 2
 3  | 3  | 3
 4  | 4  | 44
(4 rows)

postgres=# rollback;
ROLLBACK

update 方式二 sub-SELECT

postgres=# begin;
BEGIN
postgres=# 
postgres=# update t0 as a set c2=(select b.c2||b.c2 from t1 b where a.c0=b.c0);
UPDATE 4
postgres=# select * from t0;
 c0 | c1 | c2 
----+----+----
 1  | 1  | 11
 2  | 2  | 22
 3  | 3  | 
 4  | 4  | 
(4 rows)

postgres=# select *,coalesce(c2,'null') from t0;
 c0 | c1 | c2 | coalesce 
----+----+----+----------
 1  | 1  | 11 | 11
 2  | 2  | 22 | 22
 3  | 3  |    | null
 4  | 4  |    | null
(4 rows)

postgres=# rollback;
ROLLBACK

可以看出 c0=3,c0=4 行的 c2 被更新成 null

update 方式三 from_list

postgres=# begin;
BEGIN
postgres=# 
postgres=# update t0 as a set c2=b.c2||b.c2 from t1 as b where a.c0=b.c0; 
UPDATE 2
postgres=# select * from t0;
 c0 | c1 | c2 
----+----+----
 3  | 3  | 3
 4  | 4  | 4
 1  | 1  | 11
 2  | 2  | 22
(4 rows)

postgres=# rollback;
ROLLBACK

只更新到了关联上的行。

update 方式四 returning

postgres=# begin;
BEGIN
postgres=# update t0 set c2=c2||c2 where c0='4' returning *;
 c0 | c1 | c2 
----+----+----
 4  | 4  | 44
(1 row)

UPDATE 1
postgres=# select * from t0;
 c0 | c1 | c2 
----+----+----
 1  | 1  | 1
 2  | 2  | 2
 3  | 3  | 3
 4  | 4  | 44
(4 rows)

postgres=# rollback;
ROLLBACK

参考:
http://postgres.cn/docs/10/sql-update.html