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,bb,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,bb,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,bb,cc,ddd,eee}
(1 row)


3.数组是否包含某元素
MH=> select * from test2 where while_list @> '{cc}';
 id |     while_list     
----+--------------------
  1 | {aa,bb,cc,ddd,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,bb,cc,ddd,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,bb,ddd,eee}
(2 rows)


Time: 0.175 ms
MH=> 

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

6.将数组转为列

MH=> select * from test2;
 id |   while_list    
----+-----------------
  2 | {eee}
  1 | {aa,bb,ddd,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;

 

9.判断元素是否在数组中，另一种方式：&&,注意数组为null的情况

MH=> select array[1,2,3]::int[] && array[2] = true;
 ?column? 
----------
 t
(1 row)

MH=> select array[]::int[] && array[2] = true;
 ?column? 
----------
 f
(1 row)

MH=> 
MH=> select * from test2 a where   test_id = 179 and a.black_list && array['lxf_test001x'] = false ;
 test_id | test_name | test_value | test_uid | test_mode | create_ts  |                     white_list                      |                    black_list                     
-------+---------+----------+--------+---------+------------+-----------------------------------------------------+---------------------------------------------------
   179 | test    |          |      0 |       2 | 1526886347 | {luffy,luxuefeng,zhao,lxf01,lxf02,osdba,postgresxl} | {lxf_test001,lxf_test002,lxf_test003,lxf_test004}
(1 row)

MH=> select * from test2 a where   test_id = 181 and a.black_list && array['lxf_test001x'] = false ;
 test_id | test_name | test_value | test_uid | test_mode | create_ts | white_list | black_list 
-------+---------+----------+--------+---------+-----------+------------+------------
(0 rows)

MH=> select * from test_main a where   test_id = 181 and a.black_list::text[] && array['lxf_test001x'] = false ;
 test_id | test_name | test_value | test_uid | test_mode | create_ts | white_list | black_list 
-------+---------+----------+--------+---------+-----------+------------+------------
(0 rows)
[root@xt1 ~]#