PostgreSQL：如何查询text[]列中含指定时间范围值的行并创建索引？

1. 基础查询（无索引，适合小数据量） #

假设你的表名为your_table，存储时间的text[]列名为time_text_array，要查询的时间范围是13:00到16:30，可以用unnest展开数组并逐个检查：

SELECT *
FROM your_table
WHERE EXISTS (
  SELECT 1
  FROM unnest(time_text_array) AS t(time_str)
  WHERE CAST(t.time_str AS TIME) BETWEEN '13:00'::TIME AND '16:30'::TIME
);

2. 创建索引优化查询（大数据量必备） #

直接转换text[]为time[]查询会触发全表扫描，可通过以下两种方式创建索引加速：

方式一：函数索引 #

先创建一个将text[]转换为time[]的不可变函数（保证相同输入返回相同结果，符合索引创建要求）：

CREATE OR REPLACE FUNCTION text_array_to_time_array(text[])
RETURNS time[] AS $$
SELECT ARRAY(SELECT CAST(elem AS TIME) FROM unnest($1) AS elem);
$$ LANGUAGE sql IMMUTABLE;

再基于这个函数创建GIN索引（GIN索引适配数组类型的查询优化）：

CREATE INDEX idx_time_text_to_time_array ON your_table USING GIN (text_array_to_time_array(time_text_array));

方式二：生成列+索引（更直观） #

如果使用PostgreSQL 12及以上版本，可直接创建存储time[]的生成列，再建索引：

-- 添加生成列，自动将text[]转换为time[]
ALTER TABLE your_table
ADD COLUMN time_array time[] GENERATED ALWAYS AS (
  ARRAY(SELECT CAST(elem AS TIME) FROM unnest(time_text_array) AS elem)
) STORED;

-- 创建GIN索引
CREATE INDEX idx_time_array ON your_table USING GIN (time_array);

3. 利用索引加速的查询 #

基于函数索引的查询 #

SELECT *
FROM your_table
WHERE EXISTS (
  SELECT 1
  FROM unnest(text_array_to_time_array(time_text_array)) AS t(time_val)
  WHERE t.time_val <@ timerange('13:00', '16:30', '[]')
);

基于生成列的查询 #

SELECT *
FROM your_table
WHERE EXISTS (
  SELECT 1
  FROM unnest(time_array) AS t(time_val)
  WHERE t.time_val BETWEEN '13:00'::TIME AND '16:30'::TIME
);

4. 额外建议：添加格式校验约束 #

为避免text[]中出现非法时间格式（如25:61）导致转换报错，可添加CHECK约束：

ALTER TABLE your_table
ADD CONSTRAINT chk_time_text_format CHECK (
  EVERY(elem ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$')
  FROM unnest(time_text_array) AS elem
);

内容的提问来源于stack exchange，提问作者Cap