PostgreSQL数组使用(转载)_sql

概述开发的语言有数组的概念，对应于postgresql也有相关的数据字段类型，数组是英文array的翻译，可以定义一维，二维甚至更多维度，数学上跟矩阵很类似。在postgres里面可以直接存储使用，某些场景下使用很方便，也很强大。环境： OS：CentOS 6.2 DB: PostgreSQL 9.2.4 1.数组的定义不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类开发的语言有数组的概念，对应于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']] --类型不匹配

[postgres@localhost ~]$ psqlpsql (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 tablepostgres=# \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: nopostgres=# 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 tablepostgres=# \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 1postgres=# insert into t_kenyon(items) values('{3,4,5}');INSERT 0 1postgres=# insert into t_kenyon(items) values(array[6,7,8,9]);INSERT 0 1postgres=# 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 1postgres=# delete from t_kenyon where items[1] = 4;DELETE 0postgres=# delete from t_kenyon where items[1] = 3;DELETE 1

c.数据更新

往后追加postgres=# update t_kenyon set items = items||7;UPDATE 1postgres=# select * from t_kenyon;ID |  items ----+---------  1 | {1,2,7}(1 row)postgres=# update t_kenyon set items = items||'{99,66}';UPDATE 1postgres=# select * from t_kenyon;ID |      items      ----+------------------  1 | {1,55,99,66}(1 row)往前插postgres=# update t_kenyon set items = array_prepend(55,items) ;UPDATE 1postgres=# select * from t_kenyon;ID |        items       ----+---------------------  1 | {55,1,66}(1 row)

d.数据查询

postgres=# insert into t_kenyon(items) values('{3,5}');INSERT 0 1postgres=# 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 tablepostgres=# insert into t_kenyon values(1,'{1,3}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{2,4}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{34,8}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{99,12}');INSERT 0 1postgres=# create index IDx_t_kenyon on t_kenyon using gin(items);CREATE INDEXpostgres=# 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,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}

数组函数：
Function Return Type Description Example Result

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])	12 (2 rows)