Array数组类型

数组类型的声明

数组类型是通过在数组元素类型名后附加方括号[]来命名的。

示例：执行以下命令，创建一个由基础类型数组构成的表。

CREATE TABLE sal_emp (
    id              int,
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
) DISTRIBUTED BY(id);

表的详细信息如下：

• 表名：sal_emp。

• id字段：int类型，记录员工号。

• name字段：text类型字符串，记录员工姓名。

• pay_by_quarter字段：一维integer数组，记录员工每个季度的薪资。

• schedule字段：两维text数组，记录员工周计划。

• 该表按id哈希分布。

数组值写入

通过INSERT语句进行数组值写入，示例如下。

INSERT INTO sal_emp
    VALUES (1,
    'Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES (2,
    'Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

查询数据写入情况。

SELECT * FROM sal_emp;

返回结果如下。

 id | name  |      pay_by_quarter       |                 schedule
----+-------+---------------------------+-------------------------------------------
  2 | Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
  1 | Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
(2 rows)

访问数组

AnalyticDB PostgreSQL版使用以1为基的数组，即一个n元素的数组从array[1]开始，到array[n]结束。您也可以访问一个数组的任意矩形片段（子数组），对于一维或更多维数组， 可以用下标下界:下标上界表示一个数组的某个片段。

本章以查询sal_emp表中的数据为例，演示如何访问数组的一个元素。

• 示例一： 查询在第二季度薪资发生变化的员工姓名，示例如下。

  SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

  返回结果如下。

   name
  -------
   Carol
  (1 row)

• 示例二：查询所有员工第三季度的薪资，示例如下。

  SELECT pay_by_quarter[3] FROM sal_emp;

  返回结果如下。

   pay_by_quarter
  ----------------
            10000
            25000
  (2 rows)

• 示例三：查询员工Bill该周前两天的第一件计划，示例如下。

  SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

  返回结果如下。

          schedule
  ------------------------
   {{meeting},{training}}
  (1 row)

数组函数

AnalyticDB PostgreSQL版支持的数组函数如下表。

1
2

1 foo
2 bar
NULL baz

示例

• 示例一：使用array_dims函数查询数组的当前维数，示例如下。

  SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

  返回结果如下。

  array_dims
  ------------
   [1:2][1:2]
  (1 row)

• 示例二：使用array_upper和array_lower函数查询数组特定维的上界和下界，示例如下。

  SELECT array_upper(schedule, 1), array_lower(schedule, 1) FROM sal_emp WHERE name = 'Carol';

  返回结果如下。

   array_upper | array_lower
  -------------+-------------
             2 |           1
  (1 row)

• 示例三：使用array_length函数查看特定维数数组的长度。

  SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

  返回结果如下。

   array_length
  --------------
              2
  (1 row)

• 示例四：使用cardinality函数，查看返数组中所有维度元素的总数量。

  SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

  返回结果如下。

   cardinality
  -------------
             4
  (1 row)

修改数组

AnalyticDB PostgreSQL版支持修改数组，包含如下几种修改方式：

• 完全代替一个数组值，示例如下。

  UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
      WHERE name = 'Carol';

• 只更新数组中的某一个元素，示例如下。

  UPDATE sal_emp SET pay_by_quarter[4] = 15000
      WHERE name = 'Bill';

• 更新一个数组的某个片段，示例如下。

  UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
      WHERE name = 'Carol';

• 使用连接操作符||构造新的数组值。连接操作符允许把一个元素压入一维数组的开头或者结尾。示例如下。

  SELECT ARRAY[1,2] || ARRAY[3,4];

  返回结果如下。

   ?column?
  -----------
   {1,2,3,4}
  (1 row)

  连接操作符||也支持两个N维的数组，或者一个N维和一个N+1维的数组，示例如下。

  SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];

  返回结果如下。

        ?column?
  ---------------------
   {{5,6},{1,2},{3,4}}
  (1 row)

• 使用array_prepend、array_append或array_cat函数构造数组值。array_prepend和array_append只支持一维数组，array_cat支持多维数组。

  使用连接操作符比直接使用这些函数更好。实际上， 这些函数主要用于实现连接操作符。在用户定义的创建函数里直接使用函数可能有必要。通过函数构造数组值的示例如下。

  SELECT array_prepend(1, ARRAY[2,3]);
   array_prepend
  ---------------
   {1,2,3}
  (1 row)
  
  SELECT array_append(ARRAY[1,2], 3);
   array_append
  --------------
   {1,2,3}
  (1 row)
  
  SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
   array_cat
  -----------
   {1,2,3,4}
  (1 row)
  
  SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
        array_cat
  ---------------------
   {{1,2},{3,4},{5,6}}
  (1 row)
  
  SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
        array_cat
  ---------------------
   {{5,6},{1,2},{3,4}}

在数组中检索

检索一个数组中的某个数值时，必须检索该数组中的每一个值。您可以通过数组操作符检索符合条件的记录。

• 示例一：检索pay_by_quarter字段中包含10000元素的记录。

  SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

• 示例二：检索数组中pay_by_quarter字段所有元素值都等于10000的记录。

  SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

• 示例三：使用&&操作符检索，左操作数是否与右操作数重叠。

  SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

AnalyticDB PostgreSQL版支持的数组操作符如下表。

使用索引加速数组检索

当数组中包含非常多元素，需要使用@>操作符检索是否包含某个元素时，数组元素越多，检索性能也越差，您可以通过构建索引来加速。

本文以如下示例，展示如何通过索引加速数组检索。

-- 创建一个包含int数组列的表。
REATE TABLE multivalue (
    id      int,
    values  int[]
) DISTRIBUTED BY(id);

-- 向表中写入数据，共写入1000行，每行数据中的values数组包含1000000个元素。
INSERT INTO multivalue SELECT g, ARRAY(SELECT generate_series(1, 1000000)) FROM generate_series(1, 1000) as g;

在没有索引的情况下，查询数组元素中包含800000这个元素的记录，需要通过seq scan，性能较差。

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.00 rows=1 width=4) (actual time=711.216..3478.225 rows=1000 loops=1)
   ->  Seq Scan on multivalue  (cost=0.00..1.00 rows=1 width=4) (actual time=10.420..2629.403 rows=508 loops=1)
         Filter: ("values" @> '{800000}'::integer[])
 Planning time: 0.080 ms
   (slice0)    Executor memory: 39K bytes.
   (slice1)    Executor memory: 12733K bytes avg x 2 workers, 12733K bytes max (seg0).
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 3483.157 ms
(9 rows)

Time: 3483.667 ms

对数组列创建GIN索引后，相同查询通过索引扫描，性能大幅提升，耗时由3483.667毫秒（ms）下降至9.731毫秒（ms）。

CREATE INDEX idx_values on multivalue USING GIN (values);

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=12.04..14.10 rows=5 width=4) (actual time=3.727..4.127 rows=1000 loops=1)
   ->  Bitmap Heap Scan on multivalue  (cost=12.04..14.10 rows=3 width=4) (actual time=1.826..1.872 rows=508 loops=1)
         Recheck Cond: ("values" @> '{800000}'::integer[])
         ->  Bitmap Index Scan on idx_values  (cost=0.00..12.04 rows=3 width=0) (actual time=1.462..1.462 rows=508 loops=1)
               Index Cond: ("values" @> '{800000}'::integer[])
 Planning time: 0.155 ms
   (slice0)    Executor memory: 49K bytes.
   (slice1)    Executor memory: 331K bytes avg x 2 workers, 331K bytes max (seg0).  Work_mem: 9K bytes max.
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 9.138 ms
(11 rows)

Time: 9.731 ms

当然，通过GIN索引提升数组检索性能的同时，也会带来写入性能的下降（因为写入数据时需要更新索引），另外索引数据本身也会占用额外空间。业务在具体使用时，可以从查询性能，写入性能，存储空间三个维度综合来选择是否需要索引。

相关文档

更多关于Array类型的使用，请参见PostgreSQL文档。