Postgresql数组类型的简单实用

1.建表
MH=> create table test2(id int,while_list text[]);
CREATE TABLE
MH=> insert into test2 values(1,'{aa,bb,cc}');
INSERT 0 1
MH=> select * from test2;
 id | while_list 
----+------------
  1 | {aa,cc}
(1 row)
MH=> update test2 a set while_list[4] = 'ddd' where id = 1;
UPDATE 1
MH=> select * from test2;
 id |   while_list   
----+----------------
  1 | {aa,cc,ddd}
(1 row)

2.追加元素
MH=> update test2  set while_list = array_append(while_list,'eee') where id = 1;//追加
UPDATE 1
MH=> select * from test2;
 id |     while_list     
----+--------------------
  1 | {aa,ddd,eee}
(1 row)


3.数组是否包含某元素
MH=> select * from test2 where while_list @> '{cc}';
 id |     while_list     
----+--------------------
  1 | {aa,eee}
(1 row)


Time: 0.220 ms
MH=> select * from test2 where while_list @> '{ccc}';
 id | while_list 
----+------------
(0 rows)


Time: 0.209 ms
MH=> select * from test2 where while_list @> '{c}';
 id | while_list 
----+------------
(0 rows)


Time: 0.211 ms
MH=> 

4.删除指定元素
MH=> select * from test2;
 id |     while_list     
----+--------------------
  1 | {aa,eee}
  2 | {eee}
(2 rows)

Time: 0.171 ms
MH=> update test2 set while_list = array_remove(while_list,'cc') where id = 1;
UPDATE 1
Time: 105.359 ms
MH=> select * from test2;
 id |   while_list    
----+-----------------
  2 | {eee}
  1 | {aa,eee}
(2 rows)


Time: 0.175 ms
MH=> 

5.保证数组元素唯一
 添加元素前，先查询是否包含该元素；若存在，不添加；如不存在，则添加；

6.将数组转为列

MH=> select * from test2;
 id |   while_list    
----+-----------------
  2 | {eee}
  1 | {aa,eee}
  3 | 
(3 rows)

Time: 0.245 ms
MH=> select id,unnest(while_list) from test2;
 id | unnest 
----+--------
  2 | eee
  1 | aa
  1 | bb
  1 | ddd
  1 | eee
(5 rows)

Time: 0.269 ms
MH=> select id,case when while_list is not null then unnest(while_list) else '' end from test2;
 id | case 
----+------
  2 | eee
  1 | aa
  1 | bb
  1 | ddd
  1 | eee
  3 | 
(6 rows)

Time: 0.202 ms
MH=>

7.将多行转为一行，

array_to_string(array_agg( tag_name ),',') as tag
string_agg(CASE WHEN tag_name IS NULL THEN '' ELSE tag_name END,') as tag

8.字符串转数组

create or replace function tools_str2Array(  
in _originStr text,in _delimeter VARCHAR(10)  
) RETURNS text[]  
as $$  
declare _cindex INTEGER;  
declare _arrIndex INTEGER;  
DECLARE _arr_str text[];  
DECLARE _tmp_str text;  
DECLARE _debugStr text;  
BEGIN  
_arrIndex:=1;  
_cindex:=1;  
  
if _delimeter is NULL or "character_length"(_debugStr)<1 THEN  
return _arr_str;  
end IF;  
  
while _cindex<"length"(_originStr) loop  
 
_tmp_str:=split_part(_originStr,_delimeter,_arrIndex);  
 if "character_length"(_tmp_str)<1 then  
exit;  
end if;  
_arr_str:=_arr_str|| _tmp_str;  
_arrIndex:=_arrIndex+1;  
END loop;  
return _arr_str;  
end;  
$$ LANGUAGE plpgsql volatile;