postgres array_to_string和array的用法講解

更新時(shí)間：2021年01月28日 10:16:46 作者：ying_Maxwell

這篇文章主要介紹了postgres array_to_string和array的用法講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧

有三張表，分別如下：

select * from vehicle

select * from station

select * from vehicle_station

需求：

vehicle和station表示多對(duì)多的關(guān)系，需要把vehicle表對(duì)應(yīng)的station表的第二字段查出來(lái)放到一個(gè)字段，如果對(duì)應(yīng)多條，用逗號(hào)隔開放到一個(gè)字段。

解決方案：

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

結(jié)果如下：

補(bǔ)充：Postgres array 數(shù)組類型詳細(xì)使用

德哥這篇文章寫的很不錯(cuò)，在相關(guān)函數(shù)部分，尤其是 array_upper,array_lower 部分，有我自己的一些解釋。

ARRAY類型包含幾個(gè)重要的特征

維度

也就是幾維數(shù)組, 不管怎么賦值, ARRAY最終必須是個(gè)矩陣.

例1 :

ARRAY[1,2,3,4] 是一維數(shù)組,

ARRAY[[1,2],[3,4],[5,6]] 是二維數(shù)組

例2 :

ARRAY[['digoal','zhou'],['a','b',c']] 是錯(cuò)誤的. 因?yàn)榈诙€(gè)維度中的第一個(gè)array有2個(gè)元素, 而第二個(gè)array有3個(gè)元素. 不是一個(gè)矩陣. 個(gè)數(shù)必須一致.

同時(shí)類型也必須一致

例3 :

ARRAY[['digoal','zhou'],[1,2]] 是錯(cuò)誤的. 因?yàn)閇'digoal','zhou']是text[]類型, 而[1,2]是int[]類型.

元素

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

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

元素之間的分隔符, 除了box類型是分號(hào);, 其他類型的分隔符都是逗號(hào),.

擴(kuò)展性

一維數(shù)組可以擴(kuò)展, 二維數(shù)組無(wú)法擴(kuò)展.

subscript

訪問ARRAY中的元素需要提供subscript值. 默認(rèn)是從1開始編號(hào). 除非賦值的時(shí)候強(qiáng)制指定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第一個(gè)[]表示第一維度, 里面的數(shù)字代表第一維度中要訪問的subscript,

a第二個(gè)[]表示第二維度, 里面的數(shù)字代表第二維度中要訪問的subscript,

另外也可以訪問ARRAY的slice.

例2 :

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

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

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

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

分片的另一種寫法, 只要其中的一個(gè)維度用了分片寫法, 其他的維度如果沒有使用分片寫法, 默認(rèn)視為高位

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

接下來(lái)講解一下ARRAY類型的幾個(gè)常用函數(shù) :

array_dims, 返回的是各個(gè)維度中的低位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中指定維度的長(zhǎng)度或元素個(gè)數(shù), 如下 :

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 返回值都是下標(biāo) ，默認(rèn)從1開始的下標(biāo)。

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

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

下面就是強(qiáng)制指定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

下面就是強(qiáng)制指定subscript值了,

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

array_prepend, 用于在一維數(shù)組的前面插入元素, 如下

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

array_cat, 用于兩個(gè)相同維度的數(shù)組的連接, 或者一個(gè)n維數(shù)組和一個(gè)n+1維數(shù)組的連接, 如下

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

多維數(shù)組的第二維度,

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

接下來(lái)講解一下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中對(duì)ARRAY類型的維度沒有限制, 如int[]并不代表只能存儲(chǔ)一維數(shù)組, 其實(shí)可以存儲(chǔ)任意維度的ARRAY值.

- PostgreSQL中對(duì)ARRAY類型中元素的個(gè)數(shù)也沒有限制, 如int[10] , 不代表只能存儲(chǔ)10個(gè)元素.可以超出.

例如 :

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

這個(gè)例子中元素的個(gè)數(shù)和維度都超出了int[2]的限制，但是并沒有報(bào)錯(cuò)，而且數(shù)據(jù)已經(jīng)存儲(chǔ)進(jìn)去了.

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

手冊(cè)上的解釋如下 :

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.

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: