PostgreSQL 13.1 中文入门教程 PostgreSQL 数组函数和操作符

2024-02-25 开发教程 PostgreSQL 13.1 中文入门教程匿名 4℃

表 9.51显示了可以用于数组类型的专用的操作符。除此之外，表 9.1中所示的常用比较运算符也适用于数组。比较操作符逐个元素的比较数组内容，使用默认的元素数据类型的B-tree比较函数，并根据第一个差值进行排序。多维数组的元素按照行序进行访问（最后的下标变化最快）。如果两个数组的内容相同但维数不等，那么维度信息中的第一个不同将决定排序顺序（这是对PostgreSQL 8.2 之前版本的修改：老版本认为内容相同的两个数组相等，即使它们的维数或下标范围并不相同）。

表 9.51. 数组操作符

操作符描述例子
`anyarray@>anyarray`→ `boolean` 第一个数组是否包含第二个数组，也就是说，出现在第二个数组中的每个元素是否等于第一个数组中的某个元素? (重复值不需要特殊处理，因此`ARRAY[1]`和 `ARRAY[1,1]`被认为包含对方。) `ARRAY[1,4,3] @> ARRAY[3,1,3]`→ `t`
`anyarray<@anyarray`→ `boolean` 第一个数组包含在第二个数组中么? `ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]`→ `t`
`anyarray&&anyarray`→ `boolean` 这些数组有重叠么，也就是说，它们有共同的元素么? `ARRAY[1,4,3] && ARRAY[2,1]`→ `t`
`anyarray\|\|anyarray`→ `anyarray` 连接两个数组。连接空(null)或空数组是一个无操作(no-op);否则，数组必须具有相同的维度数(如第一个示例所示)，或者维度数相差一个(如第二个示例所示)。 `ARRAY[1,2,3] \|\| ARRAY[4,5,6,7]`→ `{1,2,3,4,5,6,7}` `ARRAY[1,2,3] \|\| ARRAY[[4,5,6],[7,8,9]]`→ `{{1,2,3},{4,5,6},{7,8,9}}`
`anyelement\|\|anyarray`→ `anyarray` 将元素连接到数组的前面(数组必须为空或一维的[one-dimensional])。 `3 \|\| ARRAY[4,5,6]`→ `{3,4,5,6}`
`anyarray\|\|anyelement`→ `anyarray` 将元素连接到数组的末尾(数组必须为空或一维的[one-dimensional])。 `ARRAY[4,5,6] \|\| 7`→ `{4,5,6,7}`

操作符

描述

例子

anyarray@>anyarray→ boolean

第一个数组是否包含第二个数组，也就是说，出现在第二个数组中的每个元素是否等于第一个数组中的某个元素? (重复值不需要特殊处理，因此ARRAY[1]和 ARRAY[1,1]被认为包含对方。)

ARRAY[1,4,3] @> ARRAY[3,1,3]→ t

anyarray<@anyarray→ boolean

第一个数组包含在第二个数组中么?

ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]→ t

anyarray&&anyarray→ boolean

这些数组有重叠么，也就是说，它们有共同的元素么?

ARRAY[1,4,3] && ARRAY[2,1]→ t

anyarray||anyarray→ anyarray

连接两个数组。连接空(null)或空数组是一个无操作(no-op);否则，数组必须具有相同的维度数(如第一个示例所示)，或者维度数相差一个(如第二个示例所示)。

ARRAY[1,2,3] || ARRAY[4,5,6,7]→ {1,2,3,4,5,6,7}

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]→ {{1,2,3},{4,5,6},{7,8,9}}

anyelement||anyarray→ anyarray

将元素连接到数组的前面(数组必须为空或一维的[one-dimensional])。

3 || ARRAY[4,5,6]→ {3,4,5,6}

anyarray||anyelement→ anyarray

将元素连接到数组的末尾(数组必须为空或一维的[one-dimensional])。

ARRAY[4,5,6] || 7→ {4,5,6,7}

参阅第 8.15 节获取有关数组操作符行为的更多细节。有关哪些操作符支持被索引的操作，请参阅第 11.2 节。

表 9.52展示了可以用于数组类型的函数。参阅第 8.15 节获取更多信息以及使用这些函数的例子。

表 9.52. 数组函数

函数描述例子
`array_append`( `anyarray`, `anyelement`) → `anyarray` 向一个数组的末端追加一个元素 (等同于 `anyarray\|\|anyelement`操作符)。 `array_append(ARRAY[1,2], 3)`→ `{1,2,3}`
`array_cat`( `anyarray`, `anyarray`) → `anyarray` 连接两个数组(等同于 `anyarray\|\|anyarray`操作符)。 `array_cat(ARRAY[1,2,3], ARRAY[4,5])`→ `{1,2,3,4,5}`
`array_dims`( `anyarray`) → `text` 返回数组维度的文本表示形式。 `array_dims(ARRAY[[1,2,3], [4,5,6]])`→ `[1:2][1:3]`
`array_fill`( `anyelement`, `integer[]`[, `integer[]`] ) → `anyarray` 返回一个包含给定值的拷贝的数组，其维数与第二个参数指定的长度相同。可选的第三个参数提供每个维度的下界值(默认为全部为 `1`)。 `array_fill(11, ARRAY[2,3])`→ `{{11,11,11},{11,11,11}}` `array_fill(7, ARRAY[3], ARRAY[2])`→ `[2:4]={7,7,7}`
`array_length`( `anyarray`, `integer`) → `integer` 返回请求的数组维度的长度。 `array_length(array[1,2,3], 1)`→ `3`
`array_lower`( `anyarray`, `integer`) → `integer` 返回请求的数组维度的下界。 `array_lower('[0:2]={1,2,3}'::integer[], 1)`→ `0`
`array_ndims`( `anyarray`) → `integer` 返回数组的维度数。 `array_ndims(ARRAY[[1,2,3], [4,5,6]])`→ `2`
`array_position`( `anyarray`, `anyelement`[, `integer`] ) → `integer` 返回阵列中第二个参数中第一次发生的下标，如果它不存在，则返回`NULL`。如果给出了第三个参数，则搜索从该下标开始。数组必须是一维的。比较是使用`IS NOT DISTINCT FROM`语义进行的，所以可以搜索`NULL`。 `array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon')`→ `2`
`array_positions`( `anyarray`, `anyelement`) → `integer[]` 返回作为第一个参数的数组中第二个参数所有出现的下标的数组。数组必须是一维的。使用`IS NOT DISTINCT FROM`语义完成比较，所以可以搜索`NULL`。只有当数组为`NULL`时才返回`NULL`;如果在数组中没有找到该值，则返回空数组。 `array_positions(ARRAY['A','A','B','A'], 'A')`→ `{1,2,4}`
`array_prepend`( `anyelement`, `anyarray`) → `anyarray` 在数组的开头添加一个元素(等同于`anyelement\|\|anyarray`操作符)。 `array_prepend(1, ARRAY[2,3])`→ `{1,2,3}`
`array_remove`( `anyarray`, `anyelement`) → `anyarray` 从数组中移除与给定值相等的所有元素。数组必须是一维的。使用`IS NOT DISTINCT FROM`语义完成比较，所以可以删除`NULL`。 `array_remove(ARRAY[1,2,3,2], 2)`→ `{1,3}`
`array_replace`( `anyarray`, `anyelement`, `anyelement`) → `anyarray` 将等于第二个参数的每个数组元素替换为第三个参数。 `array_replace(ARRAY[1,2,5,4], 5, 3)`→ `{1,2,3,4}`
`array_to_string`( `arrayanyarray`, `delimitertext`[, `null_stringtext`] ) → `text` 将每个数组元素转换为它的文本表现形式，并连接由`delimiter`符字符串分隔的元素。如果给出了`null_string`且不为`NULL`，则`NULL`数组项由该字符串表示;否则，它们将被省略。 `array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '')`→ `1,2,3,,5`
`array_upper`( `anyarray`, `integer`) → `integer` 返回请求的数组维度的上界。 `array_upper(ARRAY[1,8,3,7], 1)`→ `4`
`cardinality`( `anyarray`) → `integer` 返回数组中元素的总数，如果数组为空则返回0。 `cardinality(ARRAY[[1,2],[3,4]])`→ `4`
`string_to_array`( `stringtext`, `delimitertext`[, `null_stringtext`] ) → `text[]` 在出现`分隔符`时拆分`字符串`，并将剩余数据形成`文本`数组。如果`分隔符`为`NULL`，则`字符串`中的每个字符将成为数组中的单独元素。如果`分隔符`为空字符串，则该`字符串`视为单个字段。如果提供了`null_string`且不为`NULL`，那么匹配该字符串的字段将被转换为`NULL`项。 `string_to_array('xx~~yy~~zz', '~~', 'yy')`→ `{xx,NULL,zz}`
`unnest`( `anyarray`) → `setof anyelement` 将数组展开为一组行。 `unnest(ARRAY[1,2])`→ 1 2
`unnest`( `anyarray`, `anyarray`[, ... ] ) → `setof anyelement, anyelement [, ... ]` 将多个数组(可能是不同的数据类型)展开为一组行。如果数组的长度不完全相同，那么较短的数组将用`NULL`填充。这只在查询的FROM子句中允许;参见第 7.2.1.4 节。 `select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b)`→ a \| b ---+----- 1 \| foo 2 \| bar \| baz

函数

描述

例子

array_append( anyarray, anyelement) → anyarray

向一个数组的末端追加一个元素 (等同于 anyarray||anyelement操作符)。

array_append(ARRAY[1,2], 3)→ {1,2,3}

array_cat( anyarray, anyarray) → anyarray

连接两个数组(等同于 anyarray||anyarray操作符)。

array_cat(ARRAY[1,2,3], ARRAY[4,5])→ {1,2,3,4,5}

array_dims( anyarray) → text

返回数组维度的文本表示形式。

array_dims(ARRAY[[1,2,3], [4,5,6]])→ [1:2][1:3]

array_fill( anyelement, integer[][, integer[]] ) → anyarray

返回一个包含给定值的拷贝的数组，其维数与第二个参数指定的长度相同。可选的第三个参数提供每个维度的下界值(默认为全部为 1)。

array_fill(11, ARRAY[2,3])→ {{11,11,11},{11,11,11}}

array_fill(7, ARRAY[3], ARRAY[2])→ [2:4]={7,7,7}

array_length( anyarray, integer) → integer

返回请求的数组维度的长度。

array_length(array[1,2,3], 1)→ 3

array_lower( anyarray, integer) → integer

返回请求的数组维度的下界。

array_lower('[0:2]={1,2,3}'::integer[], 1)→ 0

array_ndims( anyarray) → integer

返回数组的维度数。

array_ndims(ARRAY[[1,2,3], [4,5,6]])→ 2

array_position( anyarray, anyelement[, integer] ) → integer

返回阵列中第二个参数中第一次发生的下标，如果它不存在，则返回NULL。如果给出了第三个参数，则搜索从该下标开始。数组必须是一维的。比较是使用IS NOT DISTINCT FROM语义进行的，所以可以搜索NULL。

array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon')→ 2

array_positions( anyarray, anyelement) → integer[]

返回作为第一个参数的数组中第二个参数所有出现的下标的数组。数组必须是一维的。使用IS NOT DISTINCT FROM语义完成比较，所以可以搜索NULL。只有当数组为NULL时才返回NULL;如果在数组中没有找到该值，则返回空数组。

array_positions(ARRAY['A','A','B','A'], 'A')→ {1,2,4}

array_prepend( anyelement, anyarray) → anyarray

在数组的开头添加一个元素(等同于anyelement||anyarray操作符)。

array_prepend(1, ARRAY[2,3])→ {1,2,3}

array_remove( anyarray, anyelement) → anyarray

从数组中移除与给定值相等的所有元素。数组必须是一维的。使用IS NOT DISTINCT FROM语义完成比较，所以可以删除NULL。

array_remove(ARRAY[1,2,3,2], 2)→ {1,3}

array_replace( anyarray, anyelement, anyelement) → anyarray

将等于第二个参数的每个数组元素替换为第三个参数。

array_replace(ARRAY[1,2,5,4], 5, 3)→ {1,2,3,4}

array_to_string( arrayanyarray, delimitertext[, null_stringtext] ) → text

将每个数组元素转换为它的文本表现形式，并连接由delimiter符字符串分隔的元素。如果给出了null_string且不为NULL，则NULL数组项由该字符串表示;否则，它们将被省略。

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')→ 1,2,3,*,5

array_upper( anyarray, integer) → integer

返回请求的数组维度的上界。

array_upper(ARRAY[1,8,3,7], 1)→ 4

cardinality( anyarray) → integer

返回数组中元素的总数，如果数组为空则返回0。

cardinality(ARRAY[[1,2],[3,4]])→ 4

string_to_array( stringtext, delimitertext[, null_stringtext] ) → text[]

在出现分隔符时拆分字符串，并将剩余数据形成文本数组。如果分隔符为NULL，则字符串中的每个字符将成为数组中的单独元素。如果分隔符为空字符串，则该字符串视为单个字段。如果提供了null_string且不为NULL，那么匹配该字符串的字段将被转换为NULL项。

string_to_array('xx~~yy~~zz', '~~', 'yy')→ {xx,NULL,zz}

unnest( anyarray) → setof anyelement

将数组展开为一组行。

unnest(ARRAY[1,2])→

1
 2

unnest( anyarray, anyarray[, ... ] ) → setof anyelement, anyelement [, ... ]

将多个数组(可能是不同的数据类型)展开为一组行。如果数组的长度不完全相同，那么较短的数组将用NULL填充。这只在查询的FROM子句中允许;参见第 7.2.1.4 节。

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b)→

a |  b
---+-----
 1 | foo
 2 | bar
   | baz

注意

string_to_array的行为中有两点与PostgreSQL 9.1之前的版本不同。首先，当输入串的长度为零时，它将返回一个空（无元素）数组而不是 NULL。其次，如果定界符串为 NULL，该函数会将输入划分成独立字符，而不是像以前那样返回 NULL。

也可参见第 9.21 节了解用于数组的聚集函数array_agg。

上一节： PostgreSQL 条件表达式

下一节： PostgreSQL 范围函数和运算符

"PostgreSQL 13.1 中文入门教程"的目录

PostgreSQL 13.1 中文入门教程 PostgreSQL 数组函数和操作符

注意