前端之家收集整理的这篇文章主要介绍了
PostgreSQL学习篇9.17 数组类型,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
数组类型声明:
create table testsz(id int,col1 int[]);
输入数组值:
postgres=# create table testsz(id int,col1 int[]);
CREATE TABLE
postgres=# insert into testsz values(1,'{1,2,3}');
INSERT 0 1
postgres=# insert into testsz values(2,'{4,5,3}');
INSERT 0 1
postgres=# select * from testsz;
id | col1
----+---------
1 | {1,3}
2 | {4,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,3}
(2 rows)
postgres=# update testsz set col1='{9,11,13}' where id=1;
UPDATE 1
postgres=# select * from testsz;
id | col1
----+-----------
2 | {4,3}
1 | {9,13}
(2 rows)
postgres=# update testsz set col1[3]=19 where id=1;
UPDATE 1
postgres=# select * from testsz;
id | col1
----+-----------
2 | {4,19}
(2 rows)
postgres=#
重点介绍两个函数:
unnest(anyarray):把数组变成多行返回;
array_agg():数组聚合
示例:、
postgres=# create table testfun(id int,v int);
CREATE TABLE
postgres=# insert into testfun values(1,1);
INSERT 0 1
postgres=# insert into testfun values(1,2);
INSERT 0 1
postgres=# insert into testfun values(1,3);
INSERT 0 1
postgres=# insert into testfun values(2,1);
INSERT 0 1
postgres=# insert into testfun values(2,2);
INSERT 0 1
postgres=# insert into testfun values(2,3);
INSERT 0 1
postgres=# insert into testfun values(3,5);
INSERT 0 1
postgres=# 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,3}
3 | {3,5}
2 | {3,1,3}
(3 rows)
postgres=# select * from testsz;
id | col1
----+-----------
2 | {4,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=#