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 ~]#