postgresql 数组类型

数组类型

create table test_arr(id int,arr_people test[],arr_classid int[]);
增

insert into test_arr values(1,array['a','b','c','d','e'],array[1,2,3,4,5]); INSERT 0 1 mydb=>  mydb=> select * from test_arr;  id | arr_people  | arr_classid  ----+-------------+-------------   1 | {a,b,c,d,e} | {1,2,3,4,5}    select  arr_people[2],arr_classid[5] from test_arr; arr_people | arr_classid  ------------+-------------  b          |           5 (1 row) 

或者

insert into test_arr values(2,'{f,g,h,i,j}','{6,7,8,9,10}'); INSERT 0 1 mydb=> select * from test_arr;  id | arr_people  | arr_classid   ----+-------------+--------------   1 | {a,b,c,d,e} | {1,2,3,4,5}   2 | {f,g,h,i,j} | {6,7,8,9,10} 

3.1 元素末尾增加
array_append(array,array_element);
向数组末尾添加一个元素
select array_append(array[1,2,3,4],4);
示例：
array_append函数
向id=1的arr_people数组中结尾添加元素aa

mydb=> update test_arr set arr_people=array_append(arr_people,'aa') where id=1; UPDATE 1  mydb=> select * from test_arr;  id |   arr_people   | arr_classid   ----+----------------+--------------   2 | {f,g,h,i,j}    | {6,7,8,9,10}   1 | {a,b,c,d,e,aa} | {1,2,3,4,5} (2 rows) 

删除:

3.1 数组的增删改查
删:array_remove函数

mydb=> select * from test_arr;  id |       arr_people        | arr_classid   ----+-------------------------+--------------   2 | {f,g,h,i,j}             | {6,7,8,9,10}   1 | {a,b,c,d,e,aaa,abc,edf} | {1,2,3,4,5} (2 rows)  mydb=> update test_arr set arr_people = array_remove(arr_people, 'aaa') where id = 1; UPDATE 1 mydb=> select * from test_arr;  id |     arr_people      | arr_classid   ----+---------------------+--------------   2 | {f,g,h,i,j}         | {6,7,8,9,10}   1 | {a,b,c,d,e,abc,edf} | {1,2,3,4,5} (2 rows) 

改：用如下函数修改postgresql数组元素

array_preappend函数
向id=1的arr_people数组中头部添加元素aa，此处不演示了

array_cat函数
向id=1的arr_people数组中结尾添加多个元素，ABC，edf

update test_arr set arr_people = array_cat(arr_people, ARRAY['abc','edf']) where id = 1; mydb=> update test_arr set arr_people = array_cat(arr_people, ARRAY['abc','edf']) where id = 1; UPDATE 1 mydb=> select * from test_arr;  id |       arr_people       | arr_classid   ----+------------------------+--------------   2 | {f,g,h,i,j}            | {6,7,8,9,10}   1 | {a,b,c,d,e,aa,abc,edf} | {1,2,3,4,5} 

array_length 返回数据长度
array_length(anyarray, int)

返回数组指定维度的长度，维度数是有由第二个参数指定的

mydb=> select array_length(ARRAY[[1, 2],[3, 4],[5, 6]],2);  array_length  --------------             2 (1 row)  mydb=>  select array_length(ARRAY[[1, 2],[3, 4],[5, 6]],1);  array_length  --------------             3 (1 row)  array_length  --------------             5 (1 row) 

array_ndims 返回数组维度

select array_ndims(arr_classid) from test_arr where id=2; mydb=> select array_ndims(arr_classid) from test_arr where id=2;  array_ndims  -------------            1 (1 row) 

array_position 返回某个元素第一次出现的位置

select array_position(arr_people,'aaa') from test_arr;  mydb=> select * from test_arr;  id |       arr_people       | arr_classid   ----+------------------------+--------------   2 | {f,g,h,i,j}            | {6,7,8,9,10}   1 | {a,b,c,d,e,aa,abc,edf} | {1,2,3,4,5} (2 rows)  mydb=> select *,array_position(arr_people,'aa') from test_arr;  id |       arr_people       | arr_classid  | array_position  ----+------------------------+--------------+----------------   2 | {f,g,h,i,j}            | {6,7,8,9,10} |                  1 | {a,b,c,d,e,aa,abc,edf} | {1,2,3,4,5}  |              6 array_replace(c, a , b) 返回将数组c中的a替换为b 

update test_arr set arr_people = array_replace(arr_people,'aa','aaa') where id = 1;

mydb=> update test_arr set arr_people = array_replace(arr_people,'aa','aaa') where id = 1; UPDATE 1 mydb=> select * from test_arr;  id |       arr_people        | arr_classid   ----+-------------------------+--------------   2 | {f,g,h,i,j}             | {6,7,8,9,10}   1 | {a,b,c,d,e,aaa,abc,edf} | {1,2,3,4,5} (2 rows)