postgresql----数组类型和函数

postgresql支持数组类型,可以是基本类型,也可以是用户自定义的类型。日常中使用数组类型的机会不多,但还是可以了解一下。不像C或JAVA高级语言的数组下标从0开始,postgresql数组下标从1开始,既可以指定长度,也可以不指定长度。且postgresql既支持一维数组,也支持多维数组,但是平时二维数组也就够用了。

示例1.使用ARRAY构建数组类型

---1*4的一维数组
test=# select array[1,2,3,4];
   array   
-----------
 {1,2,3,4}
(1 row)

--2*2的二维数组
test=# select array[[1,2],[3,4]];
     array     
---------------
 {{1,2},{3,4}}
(1 row)
--1*2的二维数组,基本类型为box矩形类型,且和上面相比box类型元素之间是以分号分隔的,其他所有类型的数据都是以逗号分隔的
test=# select array[box'(1,1),(0,0)',box'(0,0),(-1,-1)'];
            array            
-----------------------------
 {(1,1),(0,0);(0,0),(-1,-1)}
(1 row)

 

示例2.创建一张表,字段包含数组类型

其中int[]表示数组长度无限制,int[4]表示数组长度为4.

test=# create table tbl_array(a int[],b varchar(32)[][],c int);
CREATE TABLE
test=# insert into tbl_array (a,b,c) values (array[1,2],array[[1,2,3],[4,5,6]],1);
INSERT 0 1
test=# insert into tbl_array (a,b,c) values (array[1,2,3],array[[1,2],[4,5]],2);
INSERT 0 1
test=# select * from tbl_array ;
    a    |         b         | c 
---------+-------------------+---
 {1,2}   | {{1,2,3},{4,5,6}} | 1
 {1,2,3} | {{1,2},{4,5}}     | 2
(2 rows)

test=# select a[1],b[2] from tbl_array where c = 1;
 a | b 
---+---
 1 | 
(1 row)

test=# select a[1],b[2][1] from tbl_array where c = 1;
 a | b 
---+---
 1 | 4
(1 row)

test=# select a[1],b[2][4] from tbl_array where c = 1;
 a | b 
---+---
 1 | 
(1 row)

 

test=# update tbl_array set a[1] = 200 where a[1] = 1;
UPDATE 1
test=# select a[1],b[2][4] from tbl_array ;
  a  | b 
-----+---
 100 | 
 200 | 
(2 rows)

 

也可以使用[下标:上标]方式来查询数组连续的某些元素。

test=# select a[2:3] from tbl_array ;
   a   
-------
 {2}
 {2,3}
(2 rows)

test=# select a[1:3] from tbl_array ;
     a     
-----------
 {100,2}
 {200,2,3}
(2 rows)

 

 

数组操作符与函数

操作符

操作符 描述 示例 结果
= 相等  SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3];  t
<> 不等于  select ARRAY[1,2,3] <> ARRAY[1,2,4];  t
< 小于  select ARRAY[1,2,3] < ARRAY[1,2,4];  t
> 大于  select ARRAY[1,4,3] > ARRAY[1,2,4];  t
<= 小于或等于  select ARRAY[1,2,3] <= ARRAY[1,2,3];  t
>= 大于或等于  select ARRAY[1,4,3] >= ARRAY[1,4,3];  t
@> 包含  select ARRAY[1,4,3] @> ARRAY[3,1];  t
<@ 包含于  select ARRAY[2,7] <@ ARRAY[1,7,4,2,6];  t
&& 重叠(是否有相同元素)  select ARRAY[1,4,3] && ARRAY[2,1];  t
|| 数组与数组连接  select ARRAY[1,2,3] || ARRAY[4,5,6];  {1,2,3,4,5,6}
|| 数组与数组连接  select ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]];  {{1,2,3},{4,5,6},{7,8,9}}
|| 元素与数组连接  select 3 || ARRAY[4,5,6];  {3,4,5,6}
|| 数组与元素连接  select ARRAY[4,5,6] || 7;  {4,5,6,7}

 

函数

函数 返回类型 描述 示例 结果
array_append(anyarray,anyelement) anyarray  在数组末尾追加元素  

SELECT array_append(ARRAY[1,2], 3);

 {1,2,3}
array_cat(anyarray,anyarray) anyarray  连接两个数组  SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);  {1,2,3,4,5}
array_ndims(anyarray) int  返回数组维数  SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);  2
array_dims(anyarray) text  返回数组维数的文本表示  SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);  [1:2][1:3]
array_fill(anyelement,int[], [int[]]) anyarray 使用提供的值和维度初始化一个数组,其中anyelement是值,第一个int[]是数组的长度,第二个int[]是数组下界,下界默认是1  SELECT array_fill(7, ARRAY[3], ARRAY[2]);  [2:4]={7,7,7}
array_length(anyarray,int) int  返回数组指定维度的长度  SELECT array_length(array[1,2,3], 1);  3
array_lower(anyarray,int) int  返回数组指定维度的下界  SELECT array_lower('[0:2]={1,2,3}'::int[], 1);  0
array_position(anyarray,anyelement [int]) int  返回数组元素anyelement从数组的[,int]位置(默认为1)开始第一次出现在数组中的位置,数组必须是一维的  SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');  2
array_positions(anyarray,anyelement) int[]  返回元素在数组中的所有位置  SELECT array_positions(ARRAY['A','A','B','A'], 'A');  {1,2,4}
array_prepend(anyelement,anyarray) anyarray  在数组开头添加新的元素  SELECT array_prepend(1, ARRAY[2,3]);  {1,2,3}
array_remove(anyarray,anyelement) anyarray  从数组中删除所有的指定元素,必须是一维数组  SELECT array_remove(ARRAY[1,2,3,2], 2);  {1,3}
array_replace(anyarray,anyelementanyelement) anyarray  替换指定数组元素为新的元素  SELECT array_replace(ARRAY[1,2,5,4], 5, 3);  {1,2,3,4}
array_to_string(anyarray,text [text]) text  将数组元素使用分隔符连接为文本,NULL可以使用指定元素替换  SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');  1,2,3,*,5
array_upper(anyarray,int) int  数组指定维度的上届  SELECT array_upper(ARRAY[1,8,3,7], 1);  4
cardinality(anyarray) int  返回数组所有维度的长度总和,如果是空数组则返回0  SELECT cardinality(ARRAY[[1,2],[3,4]]);  4
string_to_array(text,text [text]) text[]  将文本使用分隔符分隔后转换为数组,如果指定第三个参数,则第三个参数在数组中被转换为NULL  SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');  {xx,NULL,zz}
unnest(anyarray) setof anyelement  将数组元素转换为行  SELECT unnest(ARRAY[1,2]);  

1
2

unnest(anyarrayanyarray[, ...]) setof anyelement, anyelement [, ...]  将多维数组转换为行集合,其中第一个数组显示为第一列,第二个数组显示为第二列,以此类推。但是这个函数只在from子句中使用  SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);  

unnest | unnest
--------+----
1 | foo
2 | bar
  | baz

posted @ 2016-07-14 21:49  alianblog  阅读(38730)  评论(1编辑  收藏