PostgreSQL中存储单独月份与年份列应选择什么数据类型

问题根源 #

PostgreSQL的DATE类型要求必须包含年、月、日三个完整的时间要素，你CSV里的月份是纯英文月名（仅含月份信息）、年份是纯数字（仅含年份信息），都不符合DATE类型的格式要求，因此会导入失败。另外你之前的建表语句末尾多了多余逗号，本身也会执行报错。

方案1：单独存储月份、年份（和CSV字段直接匹配） #

适合不需要高频做时间计算的场景，导入时不需要额外转换，修改后的建表语句如下：

CREATE TABLE IBREW (
    sales_id int,
    months varchar(20), -- 存储英文月份全称，长度覆盖所有月份单词
    years int -- 存储数字年份用整型即可
);

后续需要做时间计算时，可以用to_date(years || '-' || months, 'YYYY-Month')将两个字段合并为标准DATE类型使用。

方案2：合并为单个DATE字段存储 #

适合后续需要大量做时间维度统计、计算的场景，统一存储为标准日期格式（默认日取当月1号），修改后的建表语句如下：

CREATE TABLE IBREW (
    sales_id int,
    stat_date date -- 存储合并后的完整日期，格式为年-月-1
);

导入时使用COPY命令附带字段转换规则即可，示例命令：

COPY IBREW(sales_id, stat_date)
FROM '你的CSV文件本地绝对路径'
DELIMITER ',' CSV HEADER
(stat_date = to_date(years || '-' || months, 'YYYY-Month'));

如果你的CSV分隔符不是逗号，将DELIMITER ','中的逗号替换为实际分隔符即可

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