如何在PostgreSQL中存储含字符串与浮点数的二维数组？

嘿，刚好碰到过类似的场景！PostgreSQL的原生多维数组要求所有元素必须是同一数据类型，所以你这种每个子数组包含日期字符串和浮点数的混合结构，没法直接用text[][]或者numeric[][]这类原生数组类型。下面给你几个实用的解决方案，你可以根据自己的需求选：

方案1：自定义复合类型（类型安全，推荐） #

这是最规范的做法，先定义一个包含日期和数值的复合类型，再用这个类型的数组来存储数据。

第一步：创建复合类型 #

CREATE TYPE date_value_pair AS (
    record_date DATE,
    value NUMERIC
);

第二步：创建带复合数组的表 #

CREATE TABLE my_time_series (
    id SERIAL PRIMARY KEY,
    data_points date_value_pair[]
);

第三步：插入你的数据 #

把原数组转换成复合类型的格式插入就行：

INSERT INTO my_time_series (data_points)
VALUES (
    ARRAY[
        ('2018-05-15', 6.7580658761256265)::date_value_pair,
        ('2018-05-16', 7.413963464926804)::date_value_pair,
        ('2018-05-17', 8.801776892107043)::date_value_pair,
        ('2018-05-18', 10.292505686766823)::date_value_pair,
        ('2018-05-19', 10.292505686766823)::date_value_pair,
        ('2018-05-20', 10.292505686766823)::date_value_pair,
        ('2018-05-21', 10.292505686766823)::date_value_pair
    ]
);

为啥推荐这个？

• 类型安全：PostgreSQL会严格校验每个元素的日期和数值格式，不会存进非法数据
• 查询方便：可以直接通过复合类型的字段名提取数据，比如用(unnest(data_points)).record_date就能拿到所有日期

方案2：用JSON/JSONB类型（灵活快速） #

如果不需要严格的类型校验，或者想快速上线功能，用json或jsonb类型直接存整个数组是个不错的选择。

创建表 #

CREATE TABLE my_data_json (
    id SERIAL PRIMARY KEY,
    raw_data JSONB
);

插入数据 #

直接把你的数组转成JSON字符串插进去：

INSERT INTO my_data_json (raw_data)
VALUES (
    '[["2018-05-15", 6.7580658761256265], ["2018-05-16", 7.413963464926804], ["2018-05-17", 8.801776892107043], ["2018-05-18", 10.292505686766823], ["2018-05-19", 10.292505686766823], ["2018-05-20", 10.292505686766823], ["2018-05-21", 10.292505686766823]]'
);

查询示例 #

用JSON函数就能提取数据，比如拿到第一个元素的日期和数值：

SELECT raw_data->0->0 AS first_date, raw_data->0->1 AS first_value FROM my_data_json;

优点

• 不用提前定义类型，随便存任意JSON结构
• jsonb支持创建索引，频繁查询的场景也能hold住

方案3：拆分成单独的行（最符合关系型数据库设计） #

如果你的数据是时间序列类的，真心推荐把每个日期-数值对拆成单独的行，这是关系型数据库的标准玩法：

创建表 #

CREATE TABLE time_series_records (
    id SERIAL PRIMARY KEY,
    record_date DATE UNIQUE NOT NULL,
    value NUMERIC NOT NULL
);

插入数据 #

INSERT INTO time_series_records (record_date, value)
VALUES
('2018-05-15', 6.7580658761256265),
('2018-05-16', 7.413963464926804),
('2018-05-17', 8.801776892107043),
('2018-05-18', 10.292505686766823),
('2018-05-19', 10.292505686766823),
('2018-05-20', 10.292505686766823),
('2018-05-21', 10.292505686766823);

为啥说这是最优解？

• 完全符合关系型数据库范式，查询、维护都超级简单
• 容易创建索引，查询性能拉满
• 支持各种复杂的聚合、过滤操作，比如按日期范围查数值、计算平均值之类的

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