memo:PostgreSQL的数组函数

前端之家收集整理的这篇文章主要介绍了memo:PostgreSQL的数组函数前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

数组也是我们sql中经常使用的类型。像unnest这个函数可以把数组转换成一个子查询,可以方便地实现一些功能。利用好数组有时候会提高系统性能

比如减少JDBC执行次数,使用数组后能够使得查询条件使用索引等。

Table 9-40. Array Operators

Operator Description Example Result
= equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> not equal ARRAY[1,3] <> ARRAY[1,4] t
< less than ARRAY[1,3] < ARRAY[1,4] t
> greater than ARRAY[1,4,3] > ARRAY[1,4] t
<= less than or equal ARRAY[1,3] <= ARRAY[1,3] t
>= greater than or equal ARRAY[1,3] >= ARRAY[1,3] t
@> contains ARRAY[1,3] @> ARRAY[3,1] t
<@ is contained by ARRAY[2,7] <@ ARRAY[1,7,6] t
&& overlap (have elements in common) ARRAY[1,3] && ARRAY[2,1] t
|| array-to-array concatenation ARRAY[1,3] || ARRAY[4,5,6] {1,3,6}
|| array-to-array concatenation ARRAY[1,3] || ARRAY[[4,6],[7,8,9]] {{1,3},{4,6},{7,9}}
|| element-to-array concatenation 3 || ARRAY[4,6] {3,6}
|| array-to-element concatenation ARRAY[4,6] || 7 {4,6,7}

Table 9-41. Array Functions

Function Return Type Description Example Result
array_append(anyarray,anyelement) anyarray append an element to the end of an array array_append(ARRAY[1,2],3) {1,3}
array_cat(anyarray,anyarray) anyarray concatenate two arrays array_cat(ARRAY[1,3],ARRAY[4,5]) {1,5}
array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(ARRAY[[1,[4,6]]) 2
array_dims(anyarray) text returns a text representation of array's dimensions array_dims(ARRAY[[1,6]]) [1:2][1:3]
array_fill(anyelement,int[],[,int[]]) anyarray returns an array initialized with supplied value and dimensions,optionally with lower bounds other than 1 array_fill(7,ARRAY[3],ARRAY[2]) [2:4]={7,7}
array_length(anyarray,int) int returns the length of the requested array dimension array_length(array[1,1) 3
array_lower(anyarray,int) int returns lower bound of the requested array dimension array_lower('[0:2]={1,3}'::int[],1) 0
array_prepend(anyelement,anyarray) anyarray append an element to the beginning of an array array_prepend(1,ARRAY[2,3]) {1,3}
array_to_string(anyarray,text [,text]) text concatenates array elements using supplied delimiter and optional null string array_to_string(ARRAY[1,NULL,5],','*') 1,*,5
array_upper(anyarray,int) int returns upper bound of the requested array dimension array_upper(ARRAY[1,7],1) 4
string_to_array(text,text]) text[] splits string into array elements using supplied delimiter and optional null string string_to_array('xx~^~yy~^~zz','~^~','yy') {xx,zz}
unnest(anyarray) setof anyelement expand an array to a set of rows unnest(ARRAY[1,2])
1
2
(2 rows)
原文链接:https://www.f2er.com/postgresql/196586.html

猜你在找的Postgre SQL相关文章