postgre 数组类型

前言

最近在学习postgreSQL，发现 postgre 支持数组类型，闲着无聊整理了一些数组类型的用法。目前处于探索阶段。

1 创建表

CREATE TABLE test ( ID serial PRIMARY KEY, phone int4 [] );

查看表结构显示phone字段type：integer[]

postgres=# \d test
                             Table "public.test"
 Column |   Type    | Collation | Nullable |             Default
--------+-----------+-----------+----------+----------------------------------
 id     | integer   |           | not null | nextval('test_id_seq'::regclass)
 phone  | integer[] |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

注：在Navicat中会显示不出来数组类型，如下：

2 插入数据

1.数据插入有两种方式，如下：

insert into test(phone) values ('{1,2}');  
insert into test(phone) values ('{2,3}');  
insert into test(phone) values (array[3,4,5]);  
insert into test(phone) values (array[1,2,3]); 
insert into test(phone) values (array[1]); 

postgres=# select * from test;
 id |  phone
----+---------
  1 | {1,2}
  2 | {2,3}
  3 | {3,4,5}
  4 | {1,2,3}
  5 | {1}

3 查询

3.1 在数组中搜索

SELECT * FROM test WHERE 10000 = ANY (phone);
SELECT * FROM test WHERE 10000 = ALL (phone);

这个两个写法有局限性，只能匹配查询1个元素。尤其是All函数，相当于完全匹配10000这个元素，包含10000的不算。（或者还有更高级的写法，暂时没去探索）

3.2 数组函数和操作符

数组操作符

3.3 数组元素查询

3.3.1 查询包含： phone中包含 [1, 2]两个元素的记录

postgres=# SELECT * FROM test WHERE  phone @> array[1,2];
 id |  phone
----+---------
  1 | {1,2}
  4 | {1,2,3}
(2 rows)

3.3.2 查询包含： phone中包含1或包含2的记录

postgres=# SELECT * FROM test WHERE phone && array[1,2];
 id |  phone
----+---------
  1 | {1,2}
  2 | {2,3}
  4 | {1,2,3}
  5 | {1}
(4 rows)

注意
如果phone字段类型为 bigint[] ,当查询 3.3.2 的语句会报错：

postgres=# SELECT * FROM test_array WHERE phone && array[1,2];
ERROR:  operator does not exist: bigint[] && integer[]
LINE 1: SELECT * FROM test_array WHERE phone && array[1,2];
                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=#

所以需要类型转换：

postgres=# SELECT * FROM test_array WHERE phone && array[1,2] :: bigint[];
 id |  phone
----+---------
  1 | {1,2}
  2 | {2,3}
  4 | {1,2,3}
  5 | {1}
(4 rows)

4 修改数组

4.1 整个替换

postgres=# update test set phone ='{5,6}' where id =5;
UPDATE 1
--  或者使用 `ARRAY` 表达式语法
postgres=# update test set phone =array[7,8] where id =5;
UPDATE 1

4.2 更新单个元素

postgres=# update test set phone[1]='3' where id =5;
UPDATE 1

4.3 更新一个切片上的元素

postgres=# update test set phone[1:2 ]= '{2,3}' where id =5;
UPDATE 1

注意：
1.指定下标更新，数组越界部分会扩充并且补充空值。例如：一个数组arr有2个元素，现在更新arr[4]，则arr[3]为空值，采用这种方式扩大数组只允许使用在一维数组上
2.带下标的赋值方式允许创建下标不是从1开始的数组。例如：我们可以为arr[-2:7]赋值来创建一个下标值从-2到7的数组。

其他

1 postgre 支持多维数组,并且允许指定数组的确切大小

CREATE TABLE test_arr(
	id            bigserial,
	schedule      text[][],
	squares       integer[3][3]
);

2 默认情况下，数组的下标是从 1 开始的，但也可以指定下标的开始值，如下：

postgres=# select phone[1] from test where id =1;
 phone
-------
     1
(1 row)

另外可以查询所有记录第一个元素，去掉上面 where 条件就可以：

postgres=# select phone[1] from test;
 phone
-------
     1
     2
     3
     1
     1
(5 rows)
-- 或可以使用数组的切片：
postgres=# SELECT id, phone[1:2] FROM test;
 id | phone
----+-------
  1 | {1,2}
  2 | {2,3}
  3 | {3,4}
  4 | {1,2}
  5 | {1}
(5 rows)

文档

官方文档
中文文档