PostgreSQL数组使用

[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[]);

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的。

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)

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

往后追加

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)

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)

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

?column?

----------

t

(1 row)

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)

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)