PostgreSQL中如何存储包含字符串标识与对应数值的强类型数组？

以下是3种可行的实现方式，按强类型约束强度从高到低排序： #

方案1：自定义复合类型+复合类型数组（最推荐，强类型校验最严格） #

这是PostgreSQL原生支持的强类型实现方式，完全满足类型约束要求：

• 首先定义对应键值结构的自定义复合类型：

CREATE TYPE identifier_quantity AS (
    identifier text,
    quantity numeric
);

• 建表时直接使用该复合类型的数组作为字段类型：

CREATE TABLE your_table (
    id serial PRIMARY KEY,
    item_list identifier_quantity[]
);

• 插入&查询示例：

-- 插入数据，类型不匹配会直接触发报错
INSERT INTO your_table (item_list) 
VALUES (ARRAY[('sku_001', 15), ('sku_002', 32)]::identifier_quantity[]);

-- 查询解构数据
SELECT (unnest(item_list)).identifier, (unnest(item_list)).quantity 
FROM your_table;

方案2：并行独立数组+长度校验约束（匹配你提到的string[]+int[]拆分需求） #

如果希望完全拆分存储字符串数组和数值数组，可以通过约束保证两个数组一一对应：

• 建表时添加两个独立数组字段，并增加长度校验约束：

CREATE TABLE your_table (
    id serial PRIMARY KEY,
    identifiers text[],
    quantities numeric[],
    -- 强制两个数组长度一致，避免元素错位
    CONSTRAINT check_arr_length_match CHECK (array_length(identifiers, 1) = array_length(quantities, 1))
);

• 插入&查询示例：

-- 插入数据，长度不匹配会触发约束报错
INSERT INTO your_table (identifiers, quantities)
VALUES (ARRAY['sku_001', 'sku_002'], ARRAY[15, 32]);

-- 直接配对查询两个数组的元素
SELECT unnest(identifiers), unnest(quantities) FROM your_table;

方案3：JSONB数组+类型校验约束 #

如果不希望提前定义自定义类型，可使用JSONB数组搭配CHECK约束实现类型校验：

• 建表示例：

CREATE TABLE your_table (
    id serial PRIMARY KEY,
    item_list jsonb,
    CONSTRAINT check_item_list_format CHECK (
        jsonb_typeof(item_list) = 'array'
        AND NOT EXISTS (
            SELECT 1 FROM jsonb_array_elements(item_list) elem
            WHERE 
                jsonb_typeof(elem->'identifier') != 'string'
                OR jsonb_typeof(elem->'quantity') != 'number'
        )
    )
);

• 插入示例：

INSERT INTO your_table (item_list)
VALUES ('[{"identifier": "sku_001", "quantity": 15}, {"identifier": "sku_002", "quantity": 32}]');

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