PostgreSQL学习篇9.17 数组类型

来源：互联网发布：在线数据挖掘平台编辑：程序博客网时间：2024/05/29 18:35
数组类型声明：create table testsz(id int,col1 int[]);输入数组值：postgres=# create table testsz(id int,col1 int[]);CREATE TABLEpostgres=# insert into testsz values(1,'{1,2,3}');INSERT 0 1postgres=# insert into testsz values(2,'{4,5,3}');INSERT 0 1postgres=# select * from testsz; id |  col1   ----+---------  1 | {1,2,3}  2 | {4,5,3}(2 rows)postgres=#数组访问：postgres=# select id,col1[1] from testsz; id | col1----+------  1 |    1  2 |    4(2 rows)postgres=#数组值修改：postgres=# select * from testsz; id |  col1   ----+---------  1 | {1,2,3}  2 | {4,5,3}(2 rows)postgres=# update testsz set col1='{9,11,13}' where id=1;UPDATE 1postgres=# select * from testsz; id |   col1   ----+-----------  2 | {4,5,3}  1 | {9,11,13}(2 rows)postgres=# update testsz set col1[3]=19 where id=1;UPDATE 1postgres=# select * from testsz; id |   col1   ----+-----------  2 | {4,5,3}  1 | {9,11,19}(2 rows)postgres=#重点介绍两个函数：unnest(anyarray)：把数组变成多行返回；array_agg()：数组聚合示例：、postgres=# create table testfun(id int,v int);CREATE TABLEpostgres=# insert into testfun values(1,1);INSERT 0 1postgres=# insert into testfun values(1,2);INSERT 0 1postgres=# insert into testfun values(1,3);INSERT 0 1postgres=# insert into testfun values(2,3);INSERT 0 1postgres=# insert into testfun values(2,1);INSERT 0 1postgres=# insert into testfun values(2,2);INSERT 0 1postgres=# insert into testfun values(2,3);INSERT 0 1postgres=# insert into testfun values(3,3);INSERT 0 1postgres=# insert into testfun values(3,5);INSERT 0 1postgres=# select * from testfun; id | v----+---  1 | 1  1 | 2  1 | 3  2 | 3  2 | 1  2 | 2  2 | 3  3 | 3  3 | 5(9 rows)postgres=# select id,array_agg(v) from testfun group by id; id | array_agg----+-----------  1 | {1,2,3}  3 | {3,5}  2 | {3,1,2,3}(3 rows)postgres=# select * from testsz; id |   col1   ----+-----------  2 | {4,5,3}  1 | {9,11,19}(2 rows)postgres=# select id,unnest(col1) from testsz; id | unnest----+--------  2 |      4  2 |      5  2 |      3  1 |      9  1 |     11  1 |     19(6 rows)postgres=#
0 0