使用PostgreSQL数组类型

PostgreSQL表字段类型可以使用定长或可变长度的数组，数组的类型可以是任何数据库内建的类型、用户自定义的类型、枚举类型或组合类型。

一、数组的声明

先来看例子：

postgres=# create table a( id int primary key, vi int[], vt text[3]);
CREATE TABLE
postgres=# insert into a values (1, '{1,2,3}', array['a','b','c','d']);
INSERT 0 1
postgres=# select * from a;
 id |   vi    |    vt
----+---------+-----------
  1 | {1,2,3} | {a,b,c,d}
(1 row)
​
postgres=# \d a
                  Table "public.a"
 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id     | integer   |           | not null |
 vi     | integer[] |           |          |
 vt     | text[]    |           |          |
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
​
postgres=#

数组的定义就是通过在数组元素类型后面附加方括号来实现的。方括号中可以给一个长度数字，也可以不给。实际上，在目前的PostgreSQL中，如果在定义数组时给了长度数字，这个数字是没有什么用处的，不会限制数组的长度，如上面例子。

二、数组的输入

数组的输入有两种格式：

1、数组的输入值使用单引号加大括号来表示的，各个元素值之间用逗号分隔。（实际上在PosgreSQL中，每种类型都定义一个分隔符，只是大部分类型使用逗号分隔，分隔符定义可以参考select typname, typdelim from pg_catalog.pg_type)

2、数组构造器输入数组。数组构造器是由关键字array、一个[、数组元素值和一个]组成。

看如下例子，注意字符串数组的输入：

postgres=# truncate table a;
TRUNCATE TABLE
postgres=# insert into a values (1, '{1,2}','{ab,cd}');
INSERT 0 1
postgres=# insert into a values (2, '{1,2}','{"ab","cd"}');
INSERT 0 1
postgres=# insert into a values (3, array[1,2],array[ab,cd]);
ERROR:  column "ab" does not exist
LINE 1: insert into a values (3, array[1,2],array[ab,cd]);
                                                  ^
postgres=# insert into a values (3, array[1,2],array['ab','cd']);
INSERT 0 1
postgres=#

三、数组的访问

默认情况下，PostgreSQL数据库中数组的下标是从1开始的。可以使用数组也可以使用数组切片。

postgres=# insert into a values (1, '{1,2,3,4,5,6,7}', array['a','b','c','d','e','f','g']);
INSERT 0 1
postgres=# select vi[1],vt[1] from a;
 vi | vt
----+----
  1 | a
(1 row)
​
postgres=# select vi[3:2],vt[3:2] from a;
 vi | vt
----+----
 {} | {}
(1 row)
​
postgres=# select vi[3:4],vt[3:4] from a;
  vi   |  vt
-------+-------
 {3,4} | {c,d}
(1 row)
​
postgres=# select vi[3:4],vt[3:4] from a where vi[1] =1;
  vi   |  vt
-------+-------
 {3,4} | {c,d}
(1 row)
​
postgres=#

四、数组类型的索引

GINGeneralized Inverted Index，即广义倒排索引。GIN索引中存储一系列key,位置列表对，位置列表中存储了包含此key值得行的列表。如(1, '0,1 9,5')中，表示1这个key在0,1和9,5这两个行出现过。通过这种索引结构可以快速的查找到包含指定key的行，因此GIN索引特别适用于多值类型的元素搜索，比如支持全文搜索，数组中元素的搜索。

postgres=# select reltuples from pg_class where relname = 'a';
 reltuples
-----------
     30001
(1 row)
​
postgres=# \d a
                  Table "public.a"
 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id     | integer   |           |          |
 v      | integer[] |           |          |
​
postgres=# create index on a using gin(v);
CREATE INDEX
​
postgres=# explain analyze select * from a where v @> array[9];
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on a  (cost=15.75..309.25 rows=1000 width=41) (actual time=0.151..0.445 rows=1000 loops=1)
   Recheck Cond: (v @> '{9}'::integer[])
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on a_v_idx  (cost=0.00..15.50 rows=1000 width=0) (actual time=0.136..0.136 rows=1000 loops=1)
         Index Cond: (v @> '{9}'::integer[])
 Planning Time: 0.112 ms
 Execution Time: 0.524 ms
(7 rows)
​
postgres=#

五、数组特定位置元素的索引

数组特定位置的元素，可以建立btree索引，提供查询效率。如下例子：

postgres=# \d a
                  Table "public.a"
 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id     | integer   |           |          |
 v      | integer[] |           |          |
Indexes:
    "a_v_idx" gin (v)
​
postgres=# create index on a using btree ((v[1]));
CREATE INDEX
postgres=# analyze a;
ANALYZE
postgres=# explain analyze select * from a where v[1] = 6;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Index Scan using a_v_idx1 on a  (cost=0.28..8.30 rows=1 width=36) (actual time=0.027..0.029 rows=1 loops=1)
   Index Cond: (v[1] = 6)
 Planning Time: 0.275 ms
 Execution Time: 0.049 ms
(4 rows)
​
postgres=#

转载于:https://my.oschina.net/207miner/blog/3001154