postgresql array 操作（转发）

PostgreSQL 数组类型

PostgreSQL 支持表的字段使用定长或可变长度的一维或多维数组，数组的类型可以是任何数据库内建的类型、用户自定义的类型、枚举类型，

以及组合类型。但目前还不支持 domain 类型。

数组类型的定义就是通过在数组元素类型名后面附加中括号 [] 来实现的，中括号中可以给一个长度数字，也可以不给，

定义指定多维数组的维度也是没有意义的，数组的维度是根据实际插入的数据来确定的，如下两个语句意义是一样的：

CREATE TABLE test1 (id int, col1 int[], col2 int[10], col3 text[][]);

CREATE TABLE test2 (id int, col1 int[10], col2 int[], col3 text[]);

如何输入数组值

可以使用 ARRAY 构造器语法输入数据，一个数组构造器是一个表达式，它从自身的成员上构造一个数组值。

一个简单的数组构造器由关键字 ARRAY、[、一个或多个表示数组元素值的表达式（用逗号分隔）、] 组成

INSERT INTO test values(1, ARRAY['os', 'dba']);

INSERT INTO test values(1, ARRAY['os"dba', '123"456']);

INSERT INTO test values(1, ARRAY['os''dba', '123''456']);

// 多维数组，在向多维数组插入值时，各个维度的元素个数必须相同，否则会报错
INSERT INTO test values(1, ARRAY[['os', 'dba'],['dba', 'os']]);

访问数组

默认情况下，数组的下标是从 1 开始的，但也可以指定下标的开始值，如下：

CREATE TABLE test02 (id int[]);

INSERT INTO test02 values('[2:4] = {1, 2, 3}');

SELECT id[2], id[3], id[4] FROM test02;

指定上下标的格式为：

'[下标：上标] = [元素值1，元素值2，元素值3，...]'

可以使用数组的切片，如下所示:

SELECT id, col[1:2] FROM test02;

数组的操作符

数组类型支持一些集合关系的操作符，如下所示：

@> 包含

ARRAY[1, 2, 3] @> ARRAY[1, 2]

结果：t

ARRAY[1, 2, 3] @> ARRAY[1, 4]

结果：f

ARRAY[1, 2, 3] @> ARRAY[2, 1]

结果：t

ARRAY[[1, 2, 3]] @> ARRAY[[1, 2], [2, 3]]

结果：t

<@ 被包含于

ARRAY[1, 2] <@ ARRAY[1, 2, 3]

结果：t

ARRAY[2, 1] <@ ARRAY[1, 2, 3]

结果：t

ARRAY[1, 4] <@ ARRAY[1, 2, 3]

结果：t

ARRAY[[1, 2], [2, 4]] <@ ARRAY[1, 2, 3]

结果：f

&& 重叠，是否有共同元素

ARRAY[1, 2, 3] && ARRAY[3， 4]

结果：t

ARRAY[[1, 2], [3， 4]] && ARRAY[4， 5]

结果：t

ARRAY[1, 2] && ARRAY[3, 4]

结果：f

做集合比较时，不管数组中的元素在哪一维，都可以把它们当作集合中的一个元素，而与数组的维度没有关系。

连接操作符 “||”

同维度的数组与数组连接

ARRAY[1, 2] || ARRAY[3, 4]

结果：{1,2,3,4}

ARRAY[1, 2] || ARRAY[2, 3]

结果：{1,2,2,3}

不同维度的数组与数组连接

ARRAY[1, 2] || ARRAY[[3, 4], [5, 6]]

结果：{{1, 2}, {3, 4}, {5, 6}}

元素与数组之间的连接

1 || ARRAY[2, 3]

结果：{1, 2, 3}

ARRAY[2, 3] || 1

结果：{2, 3, 1}

1 || ARRAY[[2, 3]]

结果：报错

数组的函数

array_cat(anyarray, anyarray)

连接两个数组，返回新数组

示例：array_cat(ARRAY[1, 2], ARRAY[3, 4]) 结果：{1, 2, 3, 4}

array_cat(ARRAY[[1, 2]], ARRAY[3, 4]) 结果：{{1, 2}, {3, 4}}

array_cat(ARRAY[[1, 2]], ARRAY[[3, 4]]) 结果：{{1, 2}, {3, 4}}

array_ndims(anyarray)

返回数组的维度，返回值类型为 int

示例：array_ndims(ARRAY[1, 2, 3]) 结果：1

array_ndims(ARRAY[[1, 2, 3]]) 结果：2

array_ndims(ARRAY[[1, 2, 3], [4, 5, 6]]) 结果：2

array_ndims(ARRAY[[[1, 2, 3]]]) 结果：3

array_length(anyarray, int)

返回数组指定维度的长度，维度数是有由第二个参数指定的

示例： array_length(ARRAY[1, 2, 4]， 1) 结果：3

array_length(ARRAY[[1, 2], [3, 4], [5, 6]]， 1) 结果：3

array_length(ARRAY[[1, 2], [3, 4], [5, 6]]， 2) 结果：2

array_lower(anyarray, int)

返回数组的下标

array_upperer(anyarray, int)

返回数组的上标

array_prepend(anyelement, anyarray)

在数组的开头插入一个元素

示例： array_prepend(7, ARRAY[8, 9]) 结果：{7, 8, 9}

array_remove(anyarray, anyelement)

移除数组中为指定值的元素，只支持一维数组

示例： array_remove(ARRAY[1, 2, 3], 2) 结果： {1, 3}

array_remove(ARRAY[1, 2, 3, 2, 1, 2], 2) 结果： {1, 3. 1}

array_replace(anyarray, anyelement, anyelement)

把数组中等于指定值元素的值用另一个指定值替代

示例： array_replace(ARRAY[1, 4, 3], 4, 2) 结果：{1,2,3}

array_to_string(anyarray, text)

使用指定的分隔符（第二个参数）将数组元素连接为字符串

示例： array_to_string(ARRAY[1,2,3], ',') 结果：'1,2,3'

string_to_array(text, text)

用指定的分隔符分隔的字符串转成数组

示例：string_to_array('1,2,3', ',') 结果：{1, 2, 3}

unnest(anyarray)

把数组变成多行返回

array_agg(字段)

聚合函数

转发请著名出处：https://www.cnblogs.com/ryanzheng/p/9610182.html

--原始
-- wf."WorkflowDefinition"."StartEvents" :"18,3"
--更新后
-- wf."WorkflowDefinition"."StartEvents" :"3"

do $_$
declare 
	pwfname varchar(50)= '****申请';
	r_WorkflowDefinition record;
	array_StartEvents varchar[];
	array_FinishEvents varchar[];
	emailEvent varchar[]= array['3'];
begin 
	select into r_WorkflowDefinition * from wf."WorkflowDefinition" 
	where "Name" = pwfname limit 1;
	if(r_WorkflowDefinition."WorkflowId" is null) then
		raise notice 'warn: skip.no  r_WorkflowDefinition can be found.
		Name:%',pwfname;
	else
		array_StartEvents = array_remove(string_to_array(r_WorkflowDefinition."StartEvents",',')::int[], 3);
		array_FinishEvents =array_remove(string_to_array(r_WorkflowDefinition."FinishEvents",',')::int[], 3);
		raise notice 'info: update r_WorkflowDefinition.
		Name:%,
		IsHumanFlow, from:% to:%;
		StartEvents, from:% to:%;
		FinishEvents, from:% to:%;
		original data:%
		',pwfname,
		r_WorkflowDefinition."IsHumanFlow",false,
		r_WorkflowDefinition."StartEvents",array_to_string(array_StartEvents, ','),
		r_WorkflowDefinition."FinishEvents",array_to_string(array_FinishEvents, ','),
		to_json(r_WorkflowDefinition);
		
		UPDATE wf."WorkflowDefinition"
		SET "IsHumanFlow"=false,
		"StartEvents"=array_to_string(array_StartEvents, ','), 
		"FinishEvents"=array_to_string(array_FinishEvents, ',')
		WHERE  "WorkflowId" = r_WorkflowDefinition."WorkflowId";
	end if;
	
end $_$;

SELECT string_to_array('xx~^~yy~^~zz', '~^~');
--{xx,yy,zz}

SELECT string_to_array('18,3,1',',')::int[];
--{18,3,1}

SELECT string_to_array('18,3,1',',');
--{18,3,1}

select array_remove(string_to_array('18,3,1',',')::int[], 3) ;
--{18,1}
SELECT array_remove(array ['18','3'], '3');
--{18}


select array_remove(ARRAY[1, 2, 3], 2) ;
--{1,3}

--是否存在交集
select string_to_array('18,3,1',',')::int[] && string_to_array('2,3',',')::int[];
--true
select string_to_array('18,3,1',',')::int[] && string_to_array('2,9',',')::int[];
--false

--是否包含
select ARRAY[1, 2, 3] @> ARRAY[1, 2]  
--true

SELECT ARRAY['18','3'],string_to_array('18,3',',');
--{18,3}	{18,3}

官方文档：

array介绍： https://www.postgresql.org/docs/9.1/functions-array.html#:~:text=9.17.%20Array%20Functions%20and%20Operators%20%20%20,%20%5B1%3A2%5D%20%5B1%3A3%5D%20%208%20more%20rows%20

https://www.postgresql.org/docs/9.1/arrays.html

https://www.w3resource.com/PostgreSQL/postgresql_string_to_array-function.php

---array 
--命名表
create table arraytest(inttype int,intshuzu int[],varshuzu varchar(32)[][]);
--输入array值
INSERT INTO test values(1, ARRAY['os','dba', '123','456']);

--多维数组，在向多维数组插入值时，各个维度的元素个数必须相同，否则会报错
INSERT INTO test values(1, ARRAY[['os', 'dba'],['dba', 'os']]);

--取数组中的一个元素
(array_agg(eff_date))[1] 取数组的第一个元素（下标从1开始）

--array_to_string(anyarray, text) 使用指定的分隔符（第二个参数） 将数组元素连接为字符串  
array_to_string(ARRAY[1,2,3], ',')  结果：'1,2,3'

--string_to_array(text, text) 用指定的分隔符分隔的字符串转成数组
string_to_array('1,2,3', ',')   结果：{1, 2, 3}

--unnest(anyarray)  把数组变成多行返回
select * from book;
id | name | tag 
----+------+----------
1 | java | aa,bb,cc
2 | C++ | dd,ee
则
select name,unnest(string_to_array(tag,',')) from book;
 name | unnest 
------+--------
 java | aa
 java | bb
 java | cc
 C++  | dd
 C++  | ee

--聚合函数 把多行值合并成一行
array_agg(exp, ',') --把表达式变成数组
string_agg(exp, ',') --把表达式变成一个字符串


--array_length(anyarray, int) 返回数组指定维度的长度，维度数是有由第二个参数指定的
array_length(ARRAY[1, 2, 4]， 1)  结果：3
array_length(ARRAY[[1, 2], [3, 4], [5, 6]]， 1)  结果：3
array_length(ARRAY[[1, 2], [3, 4], [5, 6]]， 2)  结果：2
 
--同维度的数组与数组连接
ARRAY[1, 2] || ARRAY[3, 4]  

--结果：{1,2,3,4}

--@> 包含 

ARRAY[1, 2, 3] @> ARRAY[1, 2] 

--结果：t

ARRAY[1, 2, 3] @> ARRAY[1, 4] 

--结果：f


--数组包含某元素否
select * from mytable where 'Journal'=ANY(pub_types);
即语法是<value> = ANY ( <array> )。还要注意postresql中的字符串文字是用单引号写的。


--array_remove(anyarray, anyelement)  移除数组中为指定值的元素，只支持一维数组
array_remove(ARRAY[1, 2, 3], 2)             
--结果： {1, 3}
array_remove(ARRAY[1, 2, 3, 2, 1, 2], 2)   
 --结果： {1, 3. 1}
					
--array_replace(anyarray, anyelement, anyelement) 把数组中等于指定值元--素的值用另一个指定值替代  
array_replace(ARRAY[1, 4, 3], 4, 2)  
--结果：{1,2,3}

postgresql array 操作 （转发）

PostgreSQL 数组类型

如何输入数组值

访问数组

数组的操作符

数组的函数

postgresql array 操作（转发）