postgres array_to_string和array的用法講解

Posted on by

有三張表，分別如下：

select * from vehicle

select * from station

select * from vehicle_station

需求：

vehicle和station表示多對多的關系，需要把vehicle表對應的station表的第二字段查出來放到一個字段，如果對應多條，用逗號隔開放到一個字段。

解決方案：

SELECT v.*, array_to_string(ARRAY (SELECT station_name FROM station WHERE ID IN (SELECT station_id FROM vehicle_station WHERE vehicle_id = v. ID)),',') station_names FROM vehicle v

結果如下：

補充：Postgres array 數組類型詳細使用

德哥這篇文章寫的很不錯，在相關函數部分，尤其是 array_upper,array_lower 部分，有我自己的一些解釋。

ARRAY類型包含幾個重要的特征

維度

也就是幾維數組, 不管怎麼賦值, ARRAY最終必須是個矩陣.

例1 :

ARRAY[1,2,3,4] 是一維數組,

ARRAY[[1,2],[3,4],[5,6]] 是二維數組

例2 :

ARRAY[[‘digoal’,’zhou’],[‘a’,’b’,c’]] 是錯誤的. 因為第二個維度中的第一個array有2個元素, 而第二個array有3個元素. 不是一個矩陣. 個數必須一致.

同時類型也必須一致

例3 :

ARRAY[[‘digoal’,’zhou’],[1,2]] 是錯誤的. 因為[‘digoal’,’zhou’]是text[]類型, 而[1,2]是int[]類型.

元素

一維數組ARRAY[1,2,3,4] 中的4個元素分別是 1, 2, 3, 4. 這些int型的值.

二維數組ARRAY[[1,2],[3,4],[5,6]] 中的第一維度有3個元素是 ARRAY[1,2] , ARRAY[3,4] , ARRAY[5,6] 這些int[]類型的值. 第二個維度的第一個subscript的元素有兩個, 分別是1,2 . 第二個subscript 分別是3,4. 第三個subscript分別是5,6.

元素之間的分隔符, 除瞭box類型是分號;, 其他類型的分隔符都是逗號,.

擴展性

一維數組可以擴展, 二維數組無法擴展.

subscript

訪問ARRAY中的元素需要提供subscript值. 默認是從1開始編號. 除非賦值的時候強制指定subscript

例1 :

ARRAY[[1,2],[3,4],[5,6]] as a
a[1][1] = 1;
a[1][2] = 2;
a[2][1] = 3;
a[2][2] = 4;
a[3][1] = 5;
a[3][2] = 6;

a第一個[]表示第一維度, 裡面的數字代表第一維度中要訪問的subscript,

a第二個[]表示第二維度, 裡面的數字代表第二維度中要訪問的subscript,

另外也可以訪問ARRAY的slice.

例2 :

a[1:2][1:1] = {{1},{3}}

第一個[]中的1表示低位subscript, 2表示高位subscript值.

第二個[]中左邊的1表示低位subscript, 右邊的1表示高位subscript值.

a[2:3][1:2] = {{3,4},{5,6}}

分片的另一種寫法, 隻要其中的一個維度用瞭分片寫法, 其他的維度如果沒有使用分片寫法, 默認視為高位

如a[2:3][2] 等同於 a[2:3][1:2]

接下來講解一下ARRAY類型的幾個常用函數 :

array_dims, 返回的是各個維度中的低位subscript和高位subscript, 如下 :

digoal=> select array_dims(ARRAY[[1,2,3,4,5],[6,7,8,9,10]]);
 array_dims 
---------+--
 [1:2][1:5]

array_length, 返回的是array中指定維度的長度或元素個數, 如下 :

digoal=> select array_length(ARRAY[[1,2,3,4,5],[6,7,8,9,10]], 1);
 array_length 
-------+------
      2
digoal=> select array_length(ARRAY[[1,2,3,4,5],[6,7,8,9,10]], 2);
 array_length 
--------+-----
      5

註意：array_lower 和 array_upper 返回值都是下標 ，默認從1開始的下標。

array_lower, 返回的是ARRAY中指定維度的低位subscript值, 如下 :

digoal=> select array_lower(ARRAY[[1,2,3,4,5],[6,7,8,9,10]], 2);
 array_lower 
--------+----
      1

下面就是強制指定subscript值瞭,

digoal=> select array_lower('[-3:-2]={1,2}'::int[], 1);
 array_lower 
---------+----
     -3

array_upper, 返回的是ARRAY中指定維度的高位subscript值, 如下 :

digoal=> select array_upper(ARRAY[[1,2,3,4,5],[6,7,8,9,10]], 2);
 array_upper 
--------+----
      5

下面就是強制指定subscript值瞭,

digoal=> select array_upper('[-3:-2]={1,2}'::int[], 1);
 array_upper 
--------+----
     -2

array_prepend, 用於在一維數組的前面插入元素, 如下

digoal=> select array_prepend('digoal', ARRAY['francs','david']);
   array_prepend   
-------------------+---
 {digoal,francs,david}
array_append, 用於在一維數組的後面插入元素, 如下
digoal=> select array_append(ARRAY['francs','david'], 'digoal');
   array_append   
---------------+-------
 {francs,david,digoal}

array_cat, 用於兩個相同維度的數組的連接, 或者一個n維數組和一個n+1維數組的連接, 如下

digoal=> select array_cat(ARRAY['francs'], ARRAY['digoal','david']);
    array_cat    
----------------+------
 {francs,digoal,david}
digoal=> select array_cat(ARRAY['francs'], ARRAY[['digoal']]);
   array_cat   
-----------------+---
 {{francs},{digoal}}

generate_subscripts, 用於按順序返回ARRAY的指定維度的subscript(s)值, 如下 :

正向返回第一維度的subscript值.

digoal=> select generate_subscripts(a, 1) from (select ARRAY['a','b','c','d'] as a) t;
 generate_subscripts 
---------------+-----
          1
          2
          3
          4

反向返回第一維度的subscript值.

digoal=> select generate_subscripts(a, 1, true) from (select ARRAY['a','b','c','d'] as a) t;
 generate_subscripts 
-----------------+--
          4
          3
          2
          1
digoal=> select generate_subscripts(a, 1) from (select '[-5:-1]={1,2,3,4,5}'::int[] as a) t;
 generate_subscripts 
---------------+-----
         -5
         -4
         -3
         -2
         -1
digoal=> select generate_subscripts(a, 1, true) from (select '[-5:-1]={1,2,3,4,5}'::int[] as a) t;
 generate_subscripts 
---------------+-----
         -1
         -2
         -3
         -4
         -5

多維數組的第二維度,

digoal=> select generate_subscripts(a, 2) from (select '[-5:-4][2:4]={{1,2,3},{4,5,6}}'::int[] as a) t;
 generate_subscripts 
---------------+-----
          2
          3
          4

接下來講解一下ARRAY類型的操作符

digoal=> select typname,oid from pg_type where typname='anyarray';
 typname | oid 
----------+------
 anyarray | 2277

操作符如下 :

digoal=> select oprname,oprleft,oprright,oprresult,oprcode,oprrest,oprjoin from pg_operator where oprleft=2277 or oprright=2277;
 oprname | oprleft | oprright | oprresult |  oprcode   |  oprrest  |   oprjoin   
---------+---------+----------+-----------+----------------+-------------+-----------------
 ||   |  2277 |   2283 |   2277 | array_append  | -      | -
 ||   |  2283 |   2277 |   2277 | array_prepend | -      | -
 ||   |  2277 |   2277 |   2277 | array_cat   | -      | -
 =    |  2277 |   2277 |    16 | array_eq    | eqsel    | eqjoinsel
 <>   |  2277 |   2277 |    16 | array_ne    | neqsel   | neqjoinsel
 <    |  2277 |   2277 |    16 | array_lt    | scalarltsel | scalarltjoinsel
 >    |  2277 |   2277 |    16 | array_gt    | scalargtsel | scalargtjoinsel
 <=   |  2277 |   2277 |    16 | array_le    | scalarltsel | scalarltjoinsel
 >=   |  2277 |   2277 |    16 | array_ge    | scalargtsel | scalargtjoinsel
 &&   |  2277 |   2277 |    16 | arrayoverlap  | areasel   | areajoinsel
 @>   |  2277 |   2277 |    16 | arraycontains | contsel   | contjoinsel
 <@   |  2277 |   2277 |    16 | arraycontained | contsel   | contjoinsel
(12 rows)

【註意】

– PostgreSQL中對ARRAY類型的維度沒有限制, 如int[]並不代表隻能存儲一維數組, 其實可以存儲任意維度的ARRAY值.

– PostgreSQL中對ARRAY類型中元素的個數也沒有限制, 如int[10] , 不代表隻能存儲10個元素.可以超出.

例如 :

digoal=> create table array_test (id int[2]);
CREATE TABLE
digoal=> insert into array_test values (ARRAY[[1,2,3,4,5],[6,7,8,9,10]]);
INSERT 0 1

這個例子中元素的個數和維度都超出瞭int[2]的限制，但是並沒有報錯，而且數據已經存儲進去瞭.

digoal=> select * from array_test ;
       id       
---------------------+------
 {{1,2,3,4,5},{6,7,8,9,10}}

手冊上的解釋如下 :

However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：