postgresql array 数据类型

--最近在给公司开发部门培训一些pg的知识，现总结如下：


--创建一维，二维，三维数组
create table t1 (id int[]);
insert into t1 values('{1,2,3}'::int[]);
select * from t1;
create table t2 (id int[][]);
insert into t2 values('{{123,123,12},{12,23,41}}'::int[][]);
select * from t2;
create table t3 (id int[][][]);
insert into t3 values(array[[[123,123,12],[12,23,41]]]);
----注意多维数组必须匹配每个维的元素数。如果不匹配则导致错误发生
insert into t3 values(array[[[123,123,12],[12,23,41]],[[12,123,123],[123,12,121]]]);
select * from t3;



--数组运算中的一些比较符
运算符                  描述
a = b                   立方体a和b是相同的
a && b                  立方体a和b重叠
a @> b                  立方体a包含立方体b
a <@ b                  立方体a被包含在立方体b中
[a, b] < [c, d]         小于
[a, b] > [c, d]         大于

postgres=# select array[1,2,3],array[2,3,4];
  array  |  array  
---------+---------
 {1,2,3} | {2,3,4}
--两个群组中是否有重叠 
postgres=# select array[1,2,3]&&array[2,3,4];
 ?column? 
----------
 t
--第一个数组是否包含第二个数组
postgres=# select array[1,2,3]@>array[2,3,4];
 ?column? 
----------
 f
 
postgres=# select array[1,2,3]@>array[2,3];  
 ?column? 
----------
 t
--第一个数组是否被第二个数组包含 
postgres=# select array[1,2,3]<@array[2,3];
 ?column? 
----------
 f
 
postgres=# select array[1,2,3]<@array[1,2,3,4];
 ?column? 
----------
 t
(1 row)


 --pg支持数组，且支持分片访问，比如[1:2],任意只有一个数字（没有冒号）的维数是从 1 开始到声明的数字为止的
 --如果任意维数被写为一个片段，也就是，包含一个冒号，那么所有维数都被当做是片段
If any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. 
Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. 
For example, [2] is treated as [1:2], as in this example:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

--任何数组的当前维数都可以用 array_dims 函数检索：
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
array_dims
------------
[1:2][1:2]

--也可以用 array_upper 和 array_lower 函数分别返回数组特定维的上界和下界:
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'


--如果数组本身或任何下标表达式是 NULL ，那么该数组的下标表达式也将生NULL 。 从一个数组的当前范围之外抓取数据将生成一个 NULL ，而不是导致错误 


--array_length可用于返回指定维数的长充
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2







 
 --将字符串切割成数据列
 SELECT regexp_split_to_table('kenyon,love,,china,!',',');
 
  --将字符串切割成数组
 SELECT regexp_split_to_array('kenyon,love,,china,!',',');
 
 --行值转换
 postgres=# select array_agg(id),string_agg(id::text,'#'),json_agg(id) from t2 where id<=10;           
      array_agg       |     string_agg     |           json_agg           
----------------------+--------------------+------------------------------
 {2,3,4,5,6,7,8,9,10} | 2#3#4#5#6#7#8#9#10 | [2, 3, 4, 5, 6, 7, 8, 9, 10]
 
 --将列值转化成行值
 select 'column',unnest(string_to_array('a,b,c',','));