Postgresql 的先進資料型態 Array

筆者在1980年代使用Apple II 電腦,初學BASIC時,裡面最好用的東西就是 Array 了.
後來使用 Python 時,List可以說是最基礎也最重要的 working horse.
可以說 Python 就是圍繞著 List 發展.
一個語言或是產品,有一個基本簡易好用的資料結構,以此為基石,建立起宏大的架構.

在 Postgresql 中, 有了 Array,我們就可以利用他,建立許多令人驚喜的應用.
且讓我慢慢為您道來.

首先來看他的 Constructor , 使用 array[]  .

select array[1, 2, 4+3]
     , pg_typeof(array[1, 2, 4+3]) as "左邊是"
     , array[1, 2, 22.7]::integer[];

+---------+-----------+----------+
|  array  |  左邊是   |  array   |
+---------+-----------+----------+
| {1,2,7} | integer[] | {1,2,23} |
+---------+-----------+----------+

建立空(null) array
select array[]::integer[], array[]::text[];

來看他的運算子

select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprleft::regtype = 'anyarray'::regtype
    or oprright::regtype = 'anyarray'::regtype
    or oprresult::regtype = 'anyarray'::regtype
 order by oprleft, oprname, oprright, oprresult;

+------------+---------+------------+-----------+----------------+
|  oprleft   | oprname |  oprright  | oprresult |    oprcode     |
+------------+---------+------------+-----------+----------------+
| anyarray   | &&      | anyarray   | boolean   | arrayoverlap   |
| anyarray   | <       | anyarray   | boolean   | array_lt       |
| anyarray   | <=      | anyarray   | boolean   | array_le       |
| anyarray   | <>      | anyarray   | boolean   | array_ne       |
| anyarray   | <@      | anyarray   | boolean   | arraycontained |
| anyarray   | =       | anyarray   | boolean   | array_eq       |
| anyarray   | >       | anyarray   | boolean   | array_gt       |
| anyarray   | >=      | anyarray   | boolean   | array_ge       |
| anyarray   | @>      | anyarray   | boolean   | arraycontains  |
| anyarray   | ||      | anyarray   | anyarray  | array_cat      |
| anyarray   | ||      | anyelement | anyarray  | array_append   |
| anyelement | ||      | anyarray   | anyarray  | array_prepend  |
+------------+---------+------------+-----------+----------------+
(12 rows)

注意到是使用了 anyarray, 還有 anyelement.

可以搭配文件 https://www.postgresql.org/docs/current/functions-array.html#ARRAY-OPERATORS-TABLE

取值的方法

select (array[1,2,3]::integer[])[1];
+-------+
| array |
+-------+
|     1 |
+-------+

注意是從 1 開始.

select (array[1,2,3,4]::integer[])[2:3];
+-------+
| array |
+-------+
| {2,3} |
+-------+

判斷 array 所含元素的方法,有 any / some (any 等義字) 還有 all 兩種方法, 這是 Postgresql 特殊的語法.
請參考 https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.28.16

select '胖達' = any(array['玉面飛鷹', '胖達']);
+----------+
| ?column? |
+----------+
| t        |
+----------+

select '胖達' = any(array[]::text[]);

+----------+
| ?column? |
+----------+
| f        |
+----------+

select '胖達' = any(array['胖達', '胖達']);
+----------+
| ?column? |
+----------+
| t        |
+----------+

array 可以是多維的

select array[array['array可以是', '多維的'], array['但是型態', '要相同']];
+------------------------------------------+
|                  array                   |
+------------------------------------------+
| {{array可以是,多維的},{但是型態,要相同}} |
+------------------------------------------+

還有許多函數可以應用,這裡先舉些例子.

select array_prepend(0, array[1,2,3])
     , array_append(array[1,2,3], 4);

+---------------+--------------+
| array_prepend | array_append |
+---------------+--------------+
| {0,1,2,3}     | {1,2,3,4}    |
+---------------+--------------+

還有轉換成 string 

select array_to_string(array['之前解答', '一些SQL問題', '有使用接著的', 'unnest()'], '-');
+--------------------------------------------+
|              array_to_string               |
+--------------------------------------------+
| 之前解答-一些SQL問題-有使用接著的-unnest() |
+--------------------------------------------+

select unnest(array['我是使用', '另外一個', '一級屠豬士', '的帳號']);
+------------+
|   unnest   |
+------------+
| 我是使用   |
| 另外一個   |
| 一級屠豬士 |
| 的帳號     |
+------------+
(4 rows)

還有這個函數 string_to_array()

select string_to_array('Postgresql,Greenplum,蠻好用的', '-');
+-----------------------------------+
|          string_to_array          |
+-----------------------------------+
| {"Postgresql,Greenplum,蠻好用的"} |
+-----------------------------------+

unnest 還可以這樣用

select t.id, s.elem, s.nr
  from (values (1, '之前解答,一些SQL問題,有使用')
             , (2, '一個好用的,函數,叫做generate_series()')
             , (3, '它是屬於,Set Returning Functions')) as t(id, txt)
  left join lateral 
       unnest(string_to_array(t.txt, ','))
       with ordinality as s(elem, nr)   
       on true;

+----+-------------------------+----+
| id |          elem           | nr |
+----+-------------------------+----+
|  1 | 之前解答                |  1 |
|  1 | 一些SQL問題             |  2 |
|  1 | 有使用                  |  3 |
|  2 | 一個好用的              |  1 |
|  2 | 函數                    |  2 |
|  2 | 叫做generate_series()   |  3 |
|  3 | 它是屬於                |  1 |
|  3 | Set Returning Functions |  2 |
+----+-------------------------+----+
(8 rows)

select gs.sn, gs.dday
  from generate_series('2019-09-28'::timestamp
       , '2019-10-05'::timestamp
       , interval '1 day')
       with ordinality as gs(dday, sn);
+----+---------------------+
| sn |        dday         |
+----+---------------------+
|  1 | 2019-09-28 00:00:00 |
|  2 | 2019-09-29 00:00:00 |
|  3 | 2019-09-30 00:00:00 |
|  4 | 2019-10-01 00:00:00 |
|  5 | 2019-10-02 00:00:00 |
|  6 | 2019-10-03 00:00:00 |
|  7 | 2019-10-04 00:00:00 |
|  8 | 2019-10-05 00:00:00 |
+----+---------------------+
(8 rows)

今天先介紹 array 的基本觀念及用法,順帶介紹了相關的 with ordinality,
搭配使用了 lateral join, 也用了 with ordinality 讓
generate_series() 使用起來更方便.