PostgreSQL含数组列的表如何通过参数化查询批量插入？

有如下PostgreSQL表结构：

┌────────────────┬─────────────────────────────┬───────────┬──────────┬──────────────────────────────────────────────────────────────────┐
│     Column     │            Type             │ Collation │ Nullable │                             Default                              │
├────────────────┼─────────────────────────────┼───────────┼──────────┼──────────────────────────────────────────────────────────────────┤
│ id             │ bigint                      │           │ not null │ nextval('"HistoricalDataAggregatorWorkOrders_id_seq"'::regclass) │
│ inputTimeRange │ tstzrange                   │           │ not null │                                                                  │
│ outputTags     │ tag[]                       │           │ not null │                                                                  │
└────────────────┴─────────────────────────────┴───────────┴──────────┴──────────────────────────────────────────────────────────────────┘

想要通过参数化查询批量插入多行数据，最初尝试用unnest实现：

INSERT INTO "HistoricalDataAggregatorWorkOrders"
  ("inputTimeRange", "outputTags")
  SELECT * from unnest($1::tstzrange[], $2::tag[][])
  RETURNING id;

但unnest会将数组扁平化，导致结果不符合预期。已知PostgreSQL没有内置的单层unnest函数，想问当表包含数组类型列时，有没有简便的参数化批量插入方式？

1. 利用数组下标关联（最直接） #

通过generate_subscripts获取数组下标，一一对应两个输入数组的元素，避免扁平化：

INSERT INTO "HistoricalDataAggregatorWorkOrders"
  ("inputTimeRange", "outputTags")
SELECT
  $1[i],
  $2[i]
FROM generate_subscripts($1::tstzrange[], 1) AS i
RETURNING id;

注意：需保证两个输入数组长度完全一致，否则会丢失数据或报错，可额外添加长度校验条件。

2. 用JSONB传递结构化参数 #

将批量数据封装为JSON数组，通过jsonb_to_recordset解析插入，适合参数格式更灵活的场景：

INSERT INTO "HistoricalDataAggregatorWorkOrders"
  ("inputTimeRange", "outputTags")
SELECT
  ("inputTimeRange")::tstzrange,
  ("outputTags")::tag[]
FROM jsonb_to_recordset($1::jsonb) AS data(
  "inputTimeRange" text,
  "outputTags" text[]
)
RETURNING id;

调用时传入的参数示例：

[
  {"inputTimeRange": "[2024-01-01T00:00:00Z,2024-01-02T00:00:00Z)", "outputTags": ["tag1", "tag2"]},
  {"inputTimeRange": "[2024-01-02T00:00:00Z,2024-01-03T00:00:00Z)", "outputTags": ["tag3"]}
]

3. PostgreSQL 10+：unnest with ordinality 关联 #

通过WITH ORDINALITY获取数组元素的位置，再按位置关联两个数组的元素：

WITH range_data AS (
  SELECT unnest($1::tstzrange[]) AS range_val, ordinality AS pos
), tag_data AS (
  SELECT unnest($2::tag[][]) AS tag_val, ordinality AS pos
)
INSERT INTO "HistoricalDataAggregatorWorkOrders"
  ("inputTimeRange", "outputTags")
SELECT range_val, tag_val
FROM range_data
JOIN tag_data USING(pos)
RETURNING id;

同样要求两个输入数组长度一致，确保位置对应。

4. 自定义单层unnest函数（复用场景） #

如果频繁需要这类操作，可自定义函数处理多维数组的单层展开：

CREATE OR REPLACE FUNCTION unnest_single(anyarray)
RETURNS SETOF anyelement AS $$
SELECT $1[i] FROM generate_subscripts($1, 1) i;
$$ LANGUAGE sql IMMUTABLE;

之后即可简化调用：

INSERT INTO "HistoricalDataAggregatorWorkOrders"
  ("inputTimeRange", "outputTags")
SELECT unnest_single($1::tstzrange[]), unnest_single($2::tag[][])
RETURNING id;

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