postgresql - 在 postgresql 数组中存储日期

Question

我有一个大型的以人为本的历史数据集，其中包括以 YYYY、YYYY-MM 或 YYYY-MM-DD 格式记录的出生日期。我一直在想我应该为这个字段使用 date[] 数组，因为数据集经常列出两个或多个出生日期。

PG docs说支持 ISO 8601 日期，并且 ISO 8601 ( wikipedia link ) 适应精度降低，但 PG 不允许我添加精度降低的日期(例如 1882-11 表示 1882 年 11 月)。

那么，处理需要包含多个出生日期(可能类似于 1883、1882-11 或 1882-12-12)的记录的最佳方法是什么？

Answer 1

假设你有一张 table person with

+----------------+---------+----------+--------------------------------+
|      person_id |  fname  |  lname   |             bdate[]            |
+----------------+---------+----------+--------------------------------+
|              1 |  'Jhon' |  'Smith' | {1883, 1882-11, or 1882-12-12} |
+----------------+---------+----------+--------------------------------+

你不希望这样，因为那样很难搜索一个日期或更新数组。

相反，您需要一个附加表 birthdays

+-------------+------+------------+
| birthday_id | type |   bdate    |
+-------------+------+------------+
|           1 |    1 | 1883-01-01 |
|           1 |    2 | 1882-11-01 |
|           1 |    3 | 1882-12-12 |
+-------------+------+------------+

这样，即使日期保存为 1883-01-01，您也知道将是 type = 1 或 1883