开发的语言有数组的概念，对应于postgresql也有相关的数据字段类型，数组是英文array的翻译，可以定义一维，二维甚至更多维度，数学上跟矩阵很类似。在postgres里面可以直接存储使用，某些场景下使用很方便，也很强大。
环境：
OS：CentOS 6.2
DB: Postgresql 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
'{99,889}'
不合理的:
array[[1,[3]] --元素长度不一致
array[[1,['Kenyon','good']] --类型不匹配
附数组操作符:
数组函数：
参考：http://www.postgresql.org/docs/9.2/static/functions-array.html
环境：
OS：CentOS 6.2
DB: Postgresql 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
'{99,889}'
不合理的:
array[[1,[3]] --元素长度不一致
array[[1,['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,5}
3 | {6,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 1c.数据更新
往后追加
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,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,66}
(1 row) d.数据查询
postgres=# insert into t_kenyon(items) values('{3,5}');
INSERT 0 1
postgres=# select * from t_kenyon where id = 1;
id | items
----+---------------------
1 | {55,66}
(1 row)
postgres=# select * from t_kenyon where items[1] = 55;
id | items
----+---------------------
1 | {55,66}
(1 row)
postgres=# select * from t_kenyon where items[3] = 5;
id | items
----+---------
4 | {3,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,3] <= ARRAY[1,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,34}}
(1 row)
3.数组索引
postgres=# create table t_kenyon(id int,items int[]);
CREATE TABLE
postgres=# insert into t_kenyon values(1,'{1,3}');
INSERT 0 1
postgres=# insert into t_kenyon values(1,'{2,4}');
INSERT 0 1
postgres=# insert into t_kenyon values(1,'{34,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)
附数组操作符:
| = | equal | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,3] | t |
| <> | not equal | ARRAY[1,3] <> ARRAY[1,4] | t |
| < | less than | ARRAY[1,3] < ARRAY[1,4] | t |
| > | greater than | ARRAY[1,3] > ARRAY[1,4] | t |
| <= | less than or equal | ARRAY[1,3] | t |
| >= | greater than or equal | ARRAY[1,3] >= ARRAY[1,3] | t |
| @> | contains | ARRAY[1,3] @> ARRAY[3,1] | t |
| <@ | is contained by | ARRAY[2,7] <@ ARRAY[1,6] | t |
| && | overlap (have elements in common) | ARRAY[1,3] && ARRAY[2,1] | t |
| || | array-to-array concatenation | ARRAY[1,3] || ARRAY[4,5,6] | {1,3,6} |
| || | array-to-array concatenation | ARRAY[1,3] || ARRAY[[4,6],[7,9]] | {{1,3},{4,6},{7,9}} |
| || | element-to-array concatenation | 3 || ARRAY[4,6] | {3,6} |
| || | array-to-element concatenation | ARRAY[4,6] || 7 | {4,6,7} |
数组函数：
| array_append(anyarray,anyelement) | anyarray | append an element to the end of an array | array_append(ARRAY[1,3) | {1,3} |
| array_cat(anyarray,anyarray) | anyarray | concatenate two arrays | array_cat(ARRAY[1,3],ARRAY[4,5]) | {1,5} |
| array_ndims(anyarray) | int | returns the number of dimensions of the array | array_ndims(ARRAY[[1,[4,6]]) | 2 |
| array_dims(anyarray) | text | returns a text representation of array's dimensions | array_dims(ARRAY[[1,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} |
| array_length(anyarray,int) | int | returns the length of the requested array dimension | array_length(array[1,1) | 3 |
| array_lower(anyarray,int) | int | returns lower bound of the requested array dimension | array_lower('[0:2]={1,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,3} |
| array_to_string(anyarray,text[,text]) | text | concatenates array elements using supplied delimiter and optional null string | array_to_string(ARRAY[1,NULL,5],','*') | 1,*,5 |
| array_upper(anyarray,int) | int | returns upper bound of the requested array dimension | array_upper(ARRAY[1,7],1) | 4 |
| string_to_array(text,text]) | text[] | splits string into array elements using supplied delimiter and optional null string | string_to_array('xx~^~yy~^~zz','~^~','yy') | {xx,zz} |
| unnest(anyarray) | setof anyelement | expand an array to a set of rows | unnest(ARRAY[1,2]) | 1 2(2 rows) |
参考：http://www.postgresql.org/docs/9.2/static/functions-array.html
原文地址：http://my.oschina.net/Kenyon/blog/133974