postgres数组使用
环境：
os：centos 6.2
db: postgresql 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组 '{99,889}'
不合理的:
array[[1,2],[3]] --元素长度不一致
array[[1,2],['kenyon','good']] --类型不匹配
[postgres@localhost ~]$ psql
psql (9.2.4)
type help for help.
postgres=# create table t_kenyon(id serial primary key,items int[]);
notice: create table will create implicit sequence t_kenyon_id_seq for serial column t_kenyon.id
notice: create table / primary key will create implicit index t_kenyon_pkey for table t_kenyon
create table
postgres=# \d+ t_kenyon
table public.t_kenyon
column | type | modifiers | storage | stats target | description
--------+-----------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('t_kenyon_id_seq'::regclass) | plain | |
items | integer[] | | extended | |
indexes:
t_kenyon_pkey primary key, btree (id)
has oids: no
postgres=# create table t_ken(id serial primary key,items int[4]);
notice: create table will create implicit sequence t_ken_id_seq for serial column t_ken.id
notice: create table / primary key will create implicit index t_ken_pkey for table t_ken
create table
postgres=# \d+ t_ken
table public.t_ken
column | type | modifiers | storage | stats target | description
--------+-----------+----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('t_ken_id_seq'::regclass) | plain | |
items | integer[] | | extended | |
indexes:
t_ken_pkey primary key, btree (id)
has oids: no
数组的存储方式是extended的。
2.数组操作
a.数据插入，有两种方式
postgres=# insert into t_kenyon(items) values('{1,2}');
insert 0 1
postgres=# insert into t_kenyon(items) values('{3,4,5}');
insert 0 1
postgres=# insert into t_kenyon(items) values(array[6,7,8,9]);
insert 0 1
postgres=# select * from t_kenyon;
id | items
----+-----------
1 | {1,2}
2 | {3,4,5}
3 | {6,7,8,9}
(3 rows)
b.数据删除
postgres=# delete from t_kenyon where id = 3;
delete 1
postgres=# delete from t_kenyon where items[1] = 4;
delete 0
postgres=# delete from t_kenyon where items[1] = 3;
delete 1
c.数据更新
往后追加
postgres=# update t_kenyon set items = items||7;
update 1
postgres=# select * from t_kenyon;
id | items
----+---------
1 | {1,2,7}
(1 row)
postgres=# update t_kenyon set items = items||'{99,66}';
update 1
postgres=# select * from t_kenyon;
id | items
----+------------------
1 | {1,2,7,55,99,66}
(1 row)
往前插
postgres=# update t_kenyon set items = array_prepend(55,items) ;
update 1
postgres=# select * from t_kenyon;
id | items
----+---------------------
1 | {55,1,2,7,55,99,66}
(1 row)
d.数据查询
postgres=# insert into t_kenyon(items) values('{3,4,5}');
insert 0 1
postgres=# select * from t_kenyon where id = 1;
id | items
----+---------------------
1 | {55,1,2,7,55,99,66}
(1 row)
postgres=# select * from t_kenyon where items[1] = 55;
id | items
----+---------------------
1 | {55,1,2,7,55,99,66}
(1 row)
postgres=# select * from t_kenyon where items[3] = 5;
id | items
----+---------
4 | {3,4,5}
(1 row)
postgres=# select items[1],items[3],items[4] from t_kenyon;
items | items | items
-------+-------+-------
55 | 2 | 7
3 | 5 |
(2 rows)
postgres=# select unnest(items) from t_kenyon where id = 4;
unnest
--------
3
4
5
(3 rows)
e.数组比较
postgres=# select array[1,2,3]
?column?
----------
t
(1 row)
f.数组字段类型转换
postgres=# select array[['11','12'],['23','34']]::int[];
array
-------------------
{{11,12},{23,34}}
(1 row)
postgres=# select array[[11,12],[23,34]]::text[];
array
-------------------
{{11,12},{23,34}}
(1 row)
3.数组索引
postgres=# create table t_kenyon(id int,items int[]);
create table
postgres=# insert into t_kenyon values(1,'{1,2,3}');
insert 0 1
postgres=# insert into t_kenyon values(1,'{2,4}');
insert 0 1
postgres=# insert into t_kenyon values(1,'{34,7,8}');
insert 0 1
postgres=# insert into t_kenyon values(1,'{99,12}');
insert 0 1
postgres=# create index idx_t_kenyon on t_kenyon using gin(items);
create index
postgres=# set enable_seqscan = off;
postgres=# explain select * from t_kenyon where items@>array[2];
query plan
---------------------------------------------------------------------------
bitmap heap scan on t_kenyon (cost=8.00..12.01 rows=1 width=36)
recheck cond: (items @> '{2}'::integer[])
-> bitmap index scan on idx_t_kenyon (cost=0.00..8.00 rows=1 width=0)
index cond: (items @> '{2}'::integer[])
(4 rows)
附数组操作符:
operator description example result
= equal array[1.1,2.1,3.1]::int[] = array[1,2,3] t
not equal array[1,2,3] array[1,2,4] t
less than array[1,2,3] t
> greater than array[1,4,3] > array[1,2,4] t
less than or equal array[1,2,3] t
>= greater than or equal array[1,4,3] >= array[1,4,3] t
@> contains array[1,4,3] @> array[3,1] t
is contained by array[2,7] t
&& overlap (have elements in common) array[1,4,3] && array[2,1] t
|| array-to-array concatenation array[1,2,3] || array[4,5,6] {1,2,3,4,5,6}
|| array-to-array concatenation array[1,2,3] || array[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| element-to-array concatenation 3 || array[4,5,6] {3,4,5,6}
|| array-to-element concatenation array[4,5,6] || 7 {4,5,6,7}
数组函数：
function return type description example result
array_append(anyarray, anyelement) anyarray append an element to the end of an array array_append(array[1,2], 3) {1,2,3}
array_cat(anyarray, anyarray) anyarray concatenate two arrays array_cat(array[1,2,3], array[4,5]) {1,2,3,4,5}
array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(array[[1,2,3], [4,5,6]]) 2
array_dims(anyarray) text returns a text representation of array's dimensions array_dims(array[[1,2,3], [4,5,6]]) [1:2][1:3]
array_fill(anyelement, int[], [, int[]]) anyarray returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1 array_fill(7, array[3], array[2]) [2:4]={7,7,7}
array_length(anyarray, int) int returns the length of the requested array dimension array_length(array[1,2,3], 1) 3
array_lower(anyarray, int) int returns lower bound of the requested array dimension array_lower('[0:2]={1,2,3}'::int[], 1) 0
array_prepend(anyelement, anyarray) anyarray append an element to the beginning of an array array_prepend(1, array[2,3]) {1,2,3}
array_to_string(anyarray, text [, text]) text concatenates array elements using supplied delimiter and optional null string array_to_string(array[1, 2, 3, null, 5], ',', '*') 1,2,3,*,5
array_upper(anyarray, int) int returns upper bound of the requested array dimension array_upper(array[1,8,3,7], 1) 4
string_to_array(text, text [, text]) text[] splits string into array elements using supplied delimiter and optional null string string_to_array('xx~^~yy~^~zz', '~^~', 'yy') {xx,null,zz}
unnest(anyarray) setof anyelement expand an array to a set of rows unnest(array[1,2])
1
2
(2 rows)