Use the ipvs Array

Postgres array environment: OS: CentOS6.2DB: PostgreSQL9.2.41. the array definition is different. The dimension element length definition is the same in the database. The length and type of the array element must be consistent, it is represented in parentheses. Reasonable: array [1, 2] -- one-dimensional array [[1, 2], [3, 5] --

Postgres array environment: OS: CentOS 6.2 DB: PostgreSQL 9.2.4 1. the dimension element length definitions with different array definitions are the same in actual storage of the database. The length and type of the array elements must be consistent and expressed in parentheses. Reasonable: array [1, 2] -- one-dimensional array [[1, 2], [3, 5] --

Use the ipvs Array

Environment:

OS: CentOS 6.2

DB: PostgreSQL 9.2.4

1. array Definition

The actual storage of Different Dimension Element lengths is the same in the database. The length and type of array elements must be consistent and expressed in parentheses.

Reasonable:

Array [1, 2] -- one-dimensional array

Array [[99,889], [] -- two-dimensional array }'

Unreasonable:

Array [[1, 2], [3] -- the element length is inconsistent

Array [[1, 2], ['kenyon', 'good'] -- Type Mismatch

[S @ 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

The storage of arrays is extended.

2. Array Operations

A. There are two ways to insert data.

S = # insert into t_kenyon (items) values ('{1, 2 }');

INSERT 0 1

S = # insert into t_kenyon (items) values ('{3, 4, 5 }');

INSERT 0 1

S = # insert into t_kenyon (items) values (array [6, 7, 8, 9]);

INSERT 0 1

S = # select * from t_kenyon;

Id | items

---- + -----------

1 | {1, 2}

2 | {3, 4, 5}

3 | {6, 7, 8, 9}

(3 rows)

B. Data Deletion

S = # delete from t_kenyon where id = 3;

DELETE 1

S = # delete from t_kenyon where items [1] = 4;

DELETE 0

S = # delete from t_kenyon where items [1] = 3;

DELETE 1

C. Data Update

Append later

S = # update t_kenyon set items = items | 7;

UPDATE 1

S = # select * from t_kenyon;

Id | items

---- + ---------

1 | {1, 2, 7}

(1 row)

S = # update t_kenyon set items = items | '{99,66 }';

UPDATE 1

S = # select * from t_kenyon;

Id | items

---- + ------------------

1 |}

(1 row)

Forward insertion

Postgres = # update t_kenyon set items = array_prepend (55, items );

UPDATE 1

S = # select * from t_kenyon;

Id | items

---- + ---------------------

1 | {, 99, 66}

(1 row)

D. Data Query

S = # insert into t_kenyon (items) values ('{3, 4, 5 }');

INSERT 0 1

S = # select * from t_kenyon where id = 1;

Id | items

---- + ---------------------

1 | {, 99, 66}

(1 row)

S = # select * from t_kenyon where items [1] = 55;

Id | items

---- + ---------------------

1 | {, 99, 66}

(1 row)

S = # 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

------- + -------

55 | 2 | 7

3 | 5 |

(2 rows)

Postgres = # select unnest (items) from t_kenyon where id = 4;

Unnest

--------

3

4

5

(3 rows)

E. array comparison

S = # select ARRAY [1, 2, 3] <= ARRAY [1, 2, 3];

? Column?

----------

T

(1 row)

F. array field type conversion

S = # select array [['11', '12'], ['23', '34']: int [];

Array

-------------------

}}

(1 row)

S = # select array [[], []: text [];

Array

-------------------

}}

(1 row)

3. Array Index

S = # create table t_kenyon (id int, items int []);

CREATE TABLE

S = # insert into t_kenyon values (1, '{1, 2, 3 }');

INSERT 0 1

S = # insert into t_kenyon values (1, '{2, 4 }');

INSERT 0 1

S = # insert into t_kenyon values (1, '{34,7, 8 }');

INSERT 0 1

S = # insert into t_kenyon values (1, '{99,12 }');

INSERT 0 1

S = # create index idx_t_kenyon on t_kenyon using gin (items );

CREATE INDEX

Postgres = # set enable_seqscan = off;

S = # 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)

Array OPERATOR:

Operator Description Example Result

= Equal ARRAY [1.1, 2.1, 3.1]: int [] = ARRAY [, 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]> ARRAY [1, 2, 4] t

<= Less than or equal ARRAY [1, 2, 3] <= ARRAY [1, 2, 3] t

> = Greater than or equal ARRAY [, 3]> = ARRAY [, 3] t

@> Contains ARRAY [, 3] @> ARRAY [] t

<@ Is contained by ARRAY [] <@ ARRAY [,] t

& Overlap (have elements in common) ARRAY [, 3] & ARRAY [] t

| Array-to-array concatenation ARRAY [, 3] | ARRAY [, 6}

| Array-to-array concatenation ARRAY [, 3] | ARRAY [[, 6], [, 9] {, 3, 6 },{ 7, 8, 9 }}

| Element-to-array concatenation 3 | ARRAY [, 6}

| Array-to-element concatenation ARRAY [, 6] | 7}

Array functions:

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 [, 3], ARRAY []) {, 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 [[, 3], [, 6]) [] []

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, 2, 3], 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, 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 ~ ^ ~ ','~ ^ ~ ', 'Yy') {xx, NULL, zz}

Unnest (anyarray) setof anyelement expand an array to a set of rows unnest (ARRAY [1, 2])

1

2

(2 rows)