PostgreSQL数组 - 言成言成啊

PostgreSQL 中所有基础数据类型的数组都有对应的内部名称，通常是通过在基础类型前加上一个下划线 _ 来表示的。这种命名方式适用于所有基础数据类型。

常见的类型与其对应的数组类型

int4[] 或 _int4: 表示 integer 类型的数组。
int8[] 或 _int8: 表示 bigint 类型的数组。
float4[] 或 _float4: 表示 real 类型的数组。
float8[] 或 _float8: 表示 double precision 类型的数组。
text[] 或 _text: 表示 text 类型的数组。
bool[] 或 _bool: 表示 boolean 类型的数组。

PostgreSQL的数组类型支持任意维度的数组结构。只要是数组类型，即可以插入一维数组，也可以插入多维数组。

创建表

-- 使用标准数组语法
CREATE TABLE example ( 
    int_array int4 [], -- int4[] 即 integer[] 类型数组
	bigint_array int8 [], -- int8[] 即 bigint[] 类型数组
	float_array float4 [], -- float4[] 即 real[] 类型数组
	double_array float8 [], -- float8[] 即 double precision[] 类型数组
	text_array TEXT [], -- text[] 类型数组
	bool_array bool [] -- bool[] 即 boolean[] 类型数组
);
-- 使用内部数组名称
CREATE TABLE example ( 
    int_array _int4, -- _int4 等价于 int4[] 或 integer[]
	bigint_array _int8, -- _int8 等价于 int8[] 或 bigint[]
	float_array _float4, -- _float4 等价于 float4[] 或 real[]
	double_array _float8, -- _float8 等价于 float8[] 或 double precision[]
	text_array _text, -- _text 等价于 text[]
	bool_array _bool -- _bool 等价于 bool[] 或 boolean[]
);

插入数据

-- 标准数组语法
INSERT INTO example ( int_array, bigint_array, float_array, double_array, text_array, bool_array )
VALUES
	(
		ARRAY [ 1, 2, 3 ],-- int4[] 或 integer[] 数组
		ARRAY [ 10000000000, 20000000000 ],-- int8[] 或 bigint[] 数组
		ARRAY [ 1.1, 2.2, 3.3 ],-- float4[] 或 real[] 数组
		ARRAY [ 1.123456789, 2.987654321 ],-- float8[] 或 double precision[] 数组
		ARRAY [ 'Hello', 'World' ],-- text[] 数组
		ARRAY [ TRUE, FALSE, TRUE ] -- bool[] 或 boolean[] 数组
		
	);
-- 内部数组语法
INSERT INTO example ( int_array, bigint_array, float_array, double_array, text_array, bool_array )
VALUES
	( '{1, 2, 3}', -- _int4 数组，即 int4[] 或 integer[]
		'{10000000000, 20000000000}', -- _int8 数组，即 int8[] 或 bigint[]
		'{1.1, 2.2, 3.3}', -- _float4 数组，即 float4[] 或 real[]
		'{1.123456789, 2.987654321}', -- _float8 数组，即 float8[] 或 double precision[]
		'{"Hello1", "World1"}', -- _text 数组，即 text[]
		'{TRUE, FALSE, TRUE}' -- _bool 数组，即 bool[] 或 boolean[]
	);

在 PostgreSQL 中，数组查询支持多种操作符和函数，这些操作符和函数可以用于各种类型的数组操作。以下是一些常见的数组查询操作符和函数：

在PostgreSQL中，数组的索引是从1开始的，这点跟Java数组不同。

1.) 包含 (@>)

用于检查一个数组是否包含另一个数组的所有元素。

示例：

-- 查询包含指定数组的数组
-- 标准数组写法
select text_array from example where text_array @> ARRAY['Hello1']
-- 内部数组写法
select text_array from example where text_array @> '{"Hello1"}'

2.) 被包含 (<@)

用于检查一个数组是否被另一个数组包含，即第一个数组的所有元素都存在于第二个数组中。

示例：

-- 查询被指定数组包含的数组
-- 标准数组写法
select text_array from example where text_array <@ ARRAY['Hello2','World2']
-- 内部数组写法
select text_array from example where text_array <@ '{"Hello2","World2"}'

3.) 重叠 (&&)

用于检查两个数组是否有重叠元素。

示例：

1	SELECT ARRAY[1, 4, 3] && ARRAY[2, 3]; -- 返回 true

4.) 任意 (= ANY 或 ANY())

用于检查数组中是否包含指定值。

示例：

1	SELECT 3 = ANY(ARRAY[1, 2, 3]); -- 返回 true

5.) 全部 (= ALL)

用于检查指定值是否等于数组中的所有元素。

示例：

1	SELECT 3 = ALL(ARRAY[3, 3, 3]); -- 返回 true

6.) 元素位置 (array_position)

用于返回数组中第一个匹配值的位置。

示例：

1	SELECT array_position(ARRAY[1, 2, 3, 2], 2); -- 返回 2

7.) 元素位置的所有匹配 (array_positions)

用于返回数组中所有匹配值的位置。

示例：

1	SELECT array_positions(ARRAY[1, 2, 3, 2], 2); -- 返回 {2, 4}

8.) 数组连接 (||)

用于连接两个数组。

示例：

1	SELECT ARRAY[1, 2] \|\| ARRAY[3, 4]; -- 返回 {1, 2, 3, 4}

9.) 数组的长度 (array_length)

用于获取数组的长度，第二个参数表示数组的维度。

示例：

1 2	-- 查询数组的一维维度的长度 SELECT array_length(ARRAY[1, 2, 3], 1); -- 返回 3

10.) 数组的下标 (array_lower 和 array_upper)

用于获取数组的最低和最高下标，第二个参数表示数组的维度。

示例：

1	SELECT array_lower(ARRAY[1, 2, 3], 1), array_upper(ARRAY[1, 2, 3], 1); -- 返回 1 和 3

11.) 数组去除 (array_remove)

用于从数组中移除指定的值。

示例：

1 2	SELECT array_remove(ARRAY[1, 2, 3, 2], 2); -- 返回 {1, 3} SELECT array_remove(ARRAY[1, NULL, 3, NULL], NULL); -- 返回 {1, 3}

12.) 数组元素的替换 (array_replace)

用于将数组中的某个元素替换为另一个元素，第二个参数表示原始值，第三个参数表示原始值进行替换后的值。

示例：

1	SELECT array_replace(ARRAY[1, 2, 3, 2], 2, 4); -- 返回 {1, 4, 3, 4}

13.) 数组的聚合 (array_agg)

用于将多个行的值聚合为一个数组。

示例：

1	select array_agg(text_array),array_length(array_agg(text_array),1),array_length(array_agg(text_array),2) from example;-- 这实际上是个二维数组。

这些操作符和函数为 PostgreSQL 中的数组查询和操作提供了灵活且强大的工具。