postgres array_to_string和array的用法講解
有三張表,分別如下:
select * from vehicle
select * from station
select * from vehicle_station
需求:
vehicle和station表示多對(duì)多的關(guān)系,需要把vehicle表對(duì)應(yīng)的station表的第二字段查出來放到一個(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ù)組無法擴(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]
接下來講解一下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中指定維度的長度或元素個(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
接下來講解一下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}}
手冊上的解釋如下 :
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ò)誤或未考慮完全的地方,望不吝賜教。
相關(guān)文章
PostgreSQL使用MySQL外表的步驟詳解(mysql_fdw)
這篇文章主要介紹了PostgreSQL使用MySQL外表的步驟(mysql_fdw),本文通過實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01PostgreSQL教程(一):數(shù)據(jù)表詳解
這篇文章主要介紹了PostgreSQL教程(一):數(shù)據(jù)表詳解表的定義、系統(tǒng)字段、表的修改、表的權(quán)限等4大部份內(nèi)容,內(nèi)容種包括表的創(chuàng)建、刪除、修改、字段的修改、刪除、主鍵和外鍵、約束添加修改刪除等,本文講解了,需要的朋友可以參考下2015-05-05PostgreSQL截取字符串到指定字符位置詳細(xì)示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL截取字符串到指定字符位置的相關(guān)資料,PostgreSQL數(shù)據(jù)庫拼接字符串函數(shù)是一種非常重要的函數(shù),使用它可以方便地將不同的字符串進(jìn)行拼接操作,從而得到我們需要的結(jié)果,需要的朋友可以參考下2023-07-07PostgreSQL 查找當(dāng)前數(shù)據(jù)庫的所有表操作
這篇文章主要介紹了PostgreSQL 查找當(dāng)前數(shù)據(jù)庫的所有表操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12PostgreSQL 實(shí)現(xiàn)給查詢列表增加序號(hào)操作
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)給查詢列表增加序號(hào)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL數(shù)據(jù)庫性能調(diào)優(yōu)的注意點(diǎn)以及pg數(shù)據(jù)庫性能優(yōu)化方式
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫性能調(diào)優(yōu)的注意點(diǎn)以及pg數(shù)據(jù)庫性能優(yōu)化方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-03-03Postgresql開啟遠(yuǎn)程訪問的步驟全紀(jì)錄
postgre一般默認(rèn)為本地連接,不支持遠(yuǎn)程訪問,所以如果要開啟遠(yuǎn)程訪問,需要更改安裝文件的配置。下面這篇文章主要給大家介紹了關(guān)于Postgresql開啟遠(yuǎn)程訪問的相關(guān)資料,需要的朋友可以參考借鑒,下面來一起看看吧。2018-03-03開源數(shù)據(jù)庫postgreSQL13在麒麟v10sp1源碼安裝過程詳解
這篇文章主要介紹了開源數(shù)據(jù)庫postgreSQL13在麒麟v10sp1源碼安裝過程詳解,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01