postgres array_to_string和array的用法讲解

有三张表,分别如下:

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.

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • postgresql 实现将数组变为行

    有的时候需要把数组元素同表中的字段进行关系运算,首先得把array变为记录行 SELECT "unnest"(array[1,2,3]) 结果: unnest 求数组交集: SELECT "unnest"(array[1,2,3]) INTERSECT SELECT "unnest"(array[3,4,5]) 结果: unnest 3 补充:PostgreSQL单列多行变一行&一行变多行 工作中经常遇到这样一个场景,希望将某个字断查询出

  • 解决PostgreSQL Array使用中的一些小问题

    在PostgreSQL 中可以使用Array数据结构,例如 select array[1,2,3]; return {1,2,3} 但是,如果 select array[1,2,3][1]; --会报错 select (select array[1,2,3])[1] --可以使用 那么在用正则匹配函数 regexp_match 就会遇到这样的问题,如 select regexp_match('123-123', '(\d+)-(\d+)'); --return {123, 123} select

  • PostgreSQL 对数组的遍历操作

    PostgreSQL 提供了数组类型. 我来演示下如何具体使用 创建一个有数组类型字段的表. create table test_array(id serial primary key, str1 int[][][]); 插入两条测试数据. insert into test_array values (1,array[[[1,2],[3,4],[5,6]],[[20,30],[40,50],[70,100]]]); insert into test_array values (2,array[[

  • postgresql数据库使用说明_实现时间范围查询

    按照日期查询通常有好几种方法: 按照日期范围查询有好几种方法,日期字段类型一般为: Timestamp without timezone 方法一: select * from user_info where create_date >= '2015-07-01' and create_date < '2015-08-15'; 方法二: select * from user_info where create_date between '2015-07-01' and '2015-08-15';

  • 对Postgresql中的json和array使用介绍

    结合近期接触到的知识点,做了一个归纳.会持续更新 json 官网文档 http://www.postgres.cn/docs/12/datatype-json.html json的两种格式 总结:json输入快,处理慢.是精准拷贝,所以能准确存储遗留对象的原格式,如对象键顺序.jsonb输入慢,处理快.会被重新解析成json数据,不保存原对象的键顺序,并且去重相同的键值,以最后一个为准.通常,除非有特别特殊的需要(例如遗留的对象键顺序假设),大多数应用应该 更愿意把 JSON 数据存储为json

  • postgres array_to_string和array的用法讲解

    有三张表,分别如下: 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

  • JavaScript之ECharts用法讲解

    前面做项目时用到ECharts,今天特此整理一下,作为笔记,同时希望帮助更多人. 首先简单介绍一下,ECharts是一个纯JavaScript图表库,底层依赖于轻量级的Canvas类库ZRender,基于BSD开原协议,是一款非常优秀的可视化前端框架. 官网地址:http://echarts.baidu.com/ 1.首先在官网 选择合适的下载版本        http://echarts.baidu.com/download.html 2.引入Echarts <script src="

  • linux 中vim的用法讲解

    Vim 是 Linux 系统上的最著名的文本/代码编辑器,也是早年的 Vi 编辑器的加强版,而 gVim 则是其 Windows 版.它的最大特色是完全使用键盘命令进行编辑,脱离了鼠标操作虽然使得入门变得困难,但上手之后键盘流的各种巧妙组合操作却能带来极为大幅的效率提升. vim的命令的一些格式 1:vim xxx 直接打开一个xxx命名的vim文件,如果没有的话直接创建一个新的.默认光标定义到第一行 2:vim + xxx 打开光标并定义到最后一行 3:vim +num xxx 打开光标定义到

  • JavaScript中Array对象用法实例总结

    本文实例讲述了JavaScript中Array对象用法.分享给大家供大家参考,具体如下: Array数组对象有很多常用的方法和属性,现总结如下: 1. length属性,获取数组中元素的个数. 2. concat()方法,连接两个数组.将两个数组连接起来.示例如下: var names= new Array('Jack','Tom','Jim'); var ages= new Array(12,32,44); var concatArray; concatArray=names.concat(a

  • C++之boost::array的用法

    本文实例讲述了C++之boost::array的用法,分享给大家供大家参考.具体如下: 复制代码 代码如下: #include <string>  #include <iostream>  #include <boost/array.hpp>  #include <algorithm>  using namespace std;  int main()  {      boost::array<int, 5> array_temp = {{12,

  • PHP substr()函数参数解释及用法讲解

    substr(string,start,length) 参数: 1,string 即你要截取的字符串 2,start 即要截取的开始位置(0表示从从前往后数 第一个字符开始,负数表示从从后往前数) eg:start=1,表示从从前往后开始的第二个数开始截取,start=-1,表示从从后往前开始的第一(是第一不是第二哦)个数开始截取, 3,length 当为正数时,为需要截取的长度:当为负数时,即理解为去掉末尾的几个字符 eg:length=3,表示截取三个长度:length=-2,即为去掉末尾的

  • 详解MySQL中EXPLAIN解释命令及用法讲解

    1,情景描述:同事教我在mysql中用explain,于是查看了一番返回内容的含义 2,现就有用处的内容做如下记录: 1,explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了: explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_d

  • Python 创建空的list,以及append用法讲解

    Python中list的用法:如何创建list,如何表达list中的元素,如何修改和删除list 运行环境:Python 3.6.2 0.空list的创建: l = list() 或者: l = [] 1.list中元素的创建和表达 fruits = ['apple', 'banana', 'pear', 'grapes', 'pineapple', 'watermelon'] fruits[2] #从0开始数起,第三个元素 pear 2.list中元素的更改 fruits[2] = 'toma

  • Oracle常用函数Trunc及Trunc函数用法讲解

    1. Trunc( date) trunc 以指定的元素截取日期类型的数据 语法:trunc(date,[ format]) date– 日期格式的值 format–日期格式 如'mm','yyyy'等 将date从指定日期格式截取 例如: trunc(sysdate,'yyyy')='01-01月-17'(sysdate='21-11月-17'); --返回今年的第一天 trunc(sysdate,'mm')='01-11月-17'; --返回本月第一天 trunc(sysdate,'d')=

  • C++语言中std::array的用法小结(神器用法)

    摘要:在这篇文章里,将从各个角度介绍下std::array的用法,希望能带来一些启发. td::array是在C++11标准中增加的STL容器,它的设计目的是提供与原生数组类似的功能与性能.也正因此,使得std::array有很多与其他容器不同的特殊之处,比如:std::array的元素是直接存放在实例内部,而不是在堆上分配空间:std::array的大小必须在编译期确定:std::array的构造函数.析构函数和赋值操作符都是编译器隐式声明的--这让很多用惯了std::vector这类容器的程

随机推荐