Postgres数组使用

Postgres数组使用 环境： OS：CentOS 6.2 DB: PostgreSQL 9.2.4 1.数组的定义 不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。 合理的： array[1,2] --一维数组 array[[1,2],[3,5]] --

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)