PGSQL数组类型操作

前言

PostgreSQL支持表的字段使用定长或可变长度的一维或多维数组，数组的类型可以是任何数据库内建的类型、用户自定义的类型、枚举类型以及组合类型。
转载于： https://blog.csdn.net/ma286388309/article/details/129884297

1.定义数组类型

create table t_test (
	id bigint,
	name integer[],
	address text[]
);

2.新增数据

insert into t_test(id,name,address) values(1,'{1,2}','{"beijing","shanghai"}');

3.修改数组

--使用||修改
update t_test set name = name||'{3}' , address = address||'{"shenzhen"}' where id=1;
--使用array_append函数修改
update t_test set name = array_append(name,3) , address = array_append(address,'shenzhen') where id=1;

4. 数组删除

通过数组类型删除整行数据。

--如果name数组的第二个元素为1，则删除整行数据
delete from t_test where name[2] = 1;

如果要删除数组中的某个元素，需要利用array_remove函数配合update语句实现，代码如下。

update t_test set name= array_remove(name,3) where id=2;

5.数组查询

查询数组中包含2的数据：

select * from t_test where 2 = any(name)

或

select * from t_test where array_position(name,2)>0;

6. 数组类型常用函数

函数	说明	语句	结果
array_append	向数组的末尾添加元素	array_append(ARRAY[1,2], 3)	{1,2,3}
array_prepend	向数组的开头添加函数	array_prepend(1, ARRAY[2,3])	{1,2,3}
array_cat	连接两个数组	array_cat(ARRAY[1,2,3], ARRAY[4,5])	{1,2,3,4,5}
array_replace	用新值替换每个等于给定值的数组元素	array_replace(ARRAY[1,2,5,4], 5, 3)	{1,2,3,4}
array_remove	删除某个给定值的数组元素	array_remove(ARRAY[1,2,3,4],4)	{1,2,3}
[start:end]	通过下标范围数组元素	select (ARRAY[‘a’,‘b’,‘c’,‘d’])[1:2]	{a,b}
array_position	数组中指定元素出现的位置	array_position(ARRAY[1,8,3,7], 8)	2
array_dims	返回数组维数的文本表示	array_dims(ARRAY[[1,2,3], [4,5,6]])	[1:2][1:3]
array_lower	返回数组维数的下界	array_lower(ARRAY[1,2,3,4], 1)	1
array_upper	返回数组维数的上界	array_upper(ARRAY[1,2,3,4], 1)	4
array_ndims	返回数组的维数	array_ndims(ARRAY[[1,2,3], [4,5,6]])	2
array_length	返回数组维度的长度	array_length(array[1,2,3], 1)	3
cardinality	返回数组中的总元素数量，如果数组是空的则为0	cardinality(ARRAY[1,2,3,5])	4
array_to_string	将数组转换为字符串，使用分隔符连接数组元素	select array_to_string(ARRAY[1, 2, 3], ‘,’)	1,2,3
string_to_array	使用指定的分隔符把字符串分隔成数组元素	select string_to_array(‘a,b,c’,‘,’)	{a,b,c}
array_agg	把多个值合并到一个数组中	SELECT id, array_agg(label) FROM t_label group by id	{label1,label2}
unnest	分解一个数组为一组行	select unnest(ARRAY[1,2,3])	[1,2,3]