Postgresql数组类型的简单实用

概述1.建表MH=> create table test2(id int,while_list text[]);CREATE TABLEMH=> insert into test2 values(1,'{aa,bb,cc}');INSERT 0 1MH=> select * from test2; id | while_list ----+------------ 1 | {aa,b

1.建表MH=> create table test2(ID int,while_List text[]);CREATE tableMH=> insert into test2 values(1,'{aa,bb,cc}');INSERT 0 1MH=> select * from test2; ID | while_List ----+------------  1 | {aa,cc}(1 row)MH=> update test2 a set while_List[4] = 'ddd' where ID = 1;UPDATE 1MH=> 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 1MH=> 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 msMH=> select * from test2 where while_List @> '{ccc}'; ID | while_List ----+------------(0 rows)Time: 0.209 msMH=> select * from test2 where while_List @> '{c}'; ID | while_List ----+------------(0 rows)Time: 0.211 msMH=> 4.删除指定元素MH=> select * from test2; ID |     while_List     ----+--------------------  1 | {aa,eee}  2 | {eee}(2 rows)Time: 0.171 msMH=> update test2 set while_List = array_remove(while_List,'cc') where ID = 1;UPDATE 1Time: 105.359 msMH=> select * from test2; ID |   while_List    ----+-----------------  2 | {eee}  1 | {aa,eee}(2 rows)Time: 0.175 msMH=> 5.保证数组元素唯一 添加元素前，先查询是否包含该元素；若存在，不添加；如不存在，则添加；

6.将数组转为列

MH=> select * from test2; ID |   while_List    ----+-----------------  2 | {eee}  1 | {aa,eee}  3 | (3 rows)Time: 0.245 msMH=> select ID,unnest(while_List) from test2; ID | unnest ----+--------  2 | eee  1 | aa  1 | bb  1 | ddd  1 | eee(5 rows)Time: 0.269 msMH=> 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 msMH=>

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;

总结

以上是内存溢出为你收集整理的Postgresql数组类型的简单实用全部内容，希望文章能够帮你解决Postgresql数组类型的简单实用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。