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] <= 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] < ARRAY[1,2,4] t

> greater than ARRAY[1,4,3] > ARRAY[1,2,4] t

<= less than or equal ARRAY[1,2,3] <= 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] <@ ARRAY[1,7,4,2,6] 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