社区博客 | PostgreSQL数组类型

postgres=> create table test04(
             id int primary key,
             name_arr text[],
             len_arr int[]);
CREATE TABLE

postgres=> insert into test04 values (1,'{"bei","jing","shi"}','{3,4,3}');
INSERT 0 1
postgres=> select * from test04;
 id |    name_arr    | age_arr 
----+----------------+---------
  1 | {bei,jing,shi} | {3,4,3}
(1 row)

postgres=> insert into test04 values (2,array['Lu','zhi','shen'],array[2,3,4]);
INSERT 0 1
postgres=> select * from test04;
 id |    name_arr    | age_arr 
----+----------------+---------
  1 | {bei,jing,shi} | {3,4,3}
  2 | {Lu,zhi,shen}  | {2,3,4}
(2 rows)

postgres=> select * from test04;
 id |    name_arr    | len_arr 
----+----------------+---------
  1 | {zhang,ya,hui} | {5,2,3}
  2 | {Lu,zhi,shen}  | {2,3,4}
(2 rows)

postgres=> select name_arr[2],len_arr[2] from test04;
 name_arr | len_arr 
----------+---------
 ya       |       2
 zhi      |       3
(2 rows)

postgres=> update test04 set name_arr=array_append(name_arr,'beizhu');
UPDATE 2
postgres=> select * from test04;
 id |       name_arr        | len_arr 
----+-----------------------+---------
  1 | {zhang,ya,hui,beizhu} | {5,2,3}
  2 | {Lu,zhi,shen,beizhu}  | {2,3,4}
(2 rows)
postgres=> update test04 set len_arr=array_append(len_arr,6);
UPDATE 2
postgres=> select * from test04;
 id |       name_arr        |  len_arr  
----+-----------------------+-----------
  1 | {zhang,ya,hui,beizhu} | {5,2,3,6}
  2 | {Lu,zhi,shen,beizhu}  | {2,3,4,6}
(2 rows)

postgres=> update test04 set name_arr = array_remove(name_arr,'beizhu') where id=1;
UPDATE 1
postgres=> select * from test04;
 id |       name_arr       |  len_arr  
----+----------------------+-----------
  2 | {Lu,zhi,shen,beizhu} | {2,3,4,6}
  1 | {zhang,ya,hui}       | {5,2,3,6}
(2 rows)

postgres=> select * from test04 where id=2;
 id |       name_arr       |  len_arr  
----+----------------------+-----------
  2 | {Lu,zhi,shen,beizhu} | {2,3,4,6}
(2 rows)


postgres=> update test04 set name_arr[4]='ceshi' where id=2;
UPDATE 1
postgres=> select * from test04 where id=2;
 id |      name_arr       |  len_arr  
----+---------------------+-----------
  2 | {Lu,zhi,shen,ceshi} | {2,3,4,6}
(1 row)

postgres=> select array_length(array[1,3,4,5,6],1);
 array_length 
--------------
            5
(1 row)

postgres=> select array_ndims(array[1,3,4,5,6]);
 array_ndims 
-------------
           1
(1 row)
postgres=> select array_ndims(array[[1,3],[4,5]]);
 array_ndims 
-------------
           2
(1 row)

postgres=> select array_position(array['a','b','e','g','b'],'b');
 array_position 
----------------
              2
(1 row)

postgres=> select array_replace(array['a','b','e','g','b'],'b','ss');
 array_replace 
---------------
 {a,ss,e,g,ss}
(1 row)

xzxt=> select array_to_string(array['a','b','e','g','b'],',');
 array_to_string 
-----------------
 a,b,e,g,b
(1 row)


xzxt=> select array_to_string(array['a','b','e','g','b'],'|');
 array_to_string 
-----------------
 a|b|e|g|b
(1 row)

# 未处理null
xzxt=> select array_to_string(array[1,2,null,4],',');
 array_to_string 
-----------------
 1,2,4
(1 row)


# 将元素为null的替换成0
xzxt=> select array_to_string(array[1,2,null,4],',','0');
 array_to_string 
-----------------
 1,2,0,4
(1 row)