PostgreSQL数组使用(转载)
开发的语言有数组的概念，对应于postgresql也有相关的数据字段类型，数组是英文array的翻译，可以定义一维，二维甚至更多维度，数学上跟矩阵很类似。在postgres里面可以直接存储使用，某些场景下使用很方便，也很强大。环境：
OS：CentOS 6.2
DB: PostgreSQL 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array --一维数组
array[,] --二维数组
'{99,889}'
不合理的:
array[,] --元素长度不一致
array[,['Kenyon','good']] --类型不匹配
$ 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);
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);
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 = 4;
DELETE 0
postgres=# delete from t_kenyon where items = 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 = 55;
id | items
----+---------------------
1 | {55,1,2,7,55,99,66}
(1 row)
postgres=# select * from t_kenyon where items = 5;
id |items
----+---------
4 | {3,4,5}
(1 row)
postgres=# select items,items,items 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 <= ARRAY;
?column?
----------
t
(1 row)
f.数组字段类型转换
postgres=# select array[['11','12'],['23','34']]::int[];
array
-------------------
{{11,12},{23,34}}
(1 row)
postgres=# select array[,]::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;
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::int[] = ARRAY
t
<>
not equal
ARRAY <> ARRAY
t
<
less than
ARRAY < ARRAY
t
>
greater than
ARRAY > ARRAY
t
<=
less than or equal
ARRAY <= ARRAY
t
>=
greater than or equal
ARRAY >= ARRAY
t
@>
contains
ARRAY @> ARRAY
t
<@
is contained by
ARRAY <@ ARRAY
t
&&
overlap (have elements in common)
ARRAY && ARRAY
t
||
array-to-array concatenation
ARRAY || ARRAY
{1,2,3,4,5,6}
||
array-to-array concatenation
ARRAY || ARRAY[,]
{{1,2,3},{4,5,6},{7,8,9}}
||
element-to-array concatenation
3 || ARRAY
{3,4,5,6}
||
array-to-element concatenation
ARRAY || 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, 3)
{1,2,3}
array_cat(anyarray, anyarray)
anyarray
concatenate two arrays
array_cat(ARRAY, ARRAY)
{1,2,3,4,5}
array_ndims(anyarray)
int
returns the number of dimensions of the array
array_ndims(ARRAY[, ])
2
array_dims(anyarray)
text
returns a text representation of array's dimensions
array_dims(ARRAY[, ])
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, ARRAY)
={7,7,7}
array_length(anyarray, int)
int
returns the length of the requested array dimension
array_length(array, 1)
3
array_lower(anyarray, int)
int
returns lower bound of the requested array dimension
array_lower('={1,2,3}'::int[], 1)
0
array_prepend(anyelement, anyarray)
anyarray
append an element to the beginning of an array
array_prepend(1, ARRAY)
{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,*,5
array_upper(anyarray, int)
int
returns upper bound of the requested array dimension
array_upper(ARRAY, 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
(2 rows)
参考：http://www.postgresql.org/docs/9.2/static/functions-array.html
原文地址：http://my.oschina.net/Kenyon/blog/133974
页:
[1]