Postgresql日期格式

Question

我有一个数据集（.tsv格式），其中一列是出生日期。但是，数据是旧的，并且并非所有日期都是YYYY-MM-DD格式。有些条目只有出生年份（缺少出生月份和出生日期），格式为YYYY-##-##（实际上##已经插入到只有年份的数据中）。我希望将此数据集加载到我的postgres数据库中，出生日期列的数据类型为date，而不是string，这样我就可以对日期进行比较
下面显示了一个小样本。（未显示不相关的数据列）

1924-##-##
1965-09-04
1944-11-05
1951-##-##
-388-##-##
1893-01-26
1037-##-##

直接批量加载数据集显然会产生错误

ERROR:  invalid input syntax for type date: "1924-##-##"
LINE 1: insert into d values ('1924-##-##');
                              ^

数据集相当大，有大约600万个条目。目前，我正在考虑运行一个脚本，用01替换这些##，然后将修改后的数据插入数据库。但我不喜欢的这个想法

1. 这很耗时
2. 这会占用磁盘空间（因为我想保留原始的"umtamped"数据）
3. 此外，我的数据库中并不是所有的数据都是真实的

我有没有办法让postgres忽略"##"（只保留缺少月份和日期的年份），以某种方式保持日期不变
或者这个问题还有更好的解决方案吗？

Answer 1

您可以在表中创建两列，一列用于最初输入的值（varchar类型），另一列用于计算（日期类型）。

CREATE TABLE your_table
(
 id INT,
 -- OTHER DETAILS
 dob_entered    VARCHAR,
 dob_parsed DATE
);

然后，您可以在Insert触发器上使用来自动填充varchar中的日期字段，并使用update触发器来处理任何更改。

CREATE OR REPLACE FUNCTION evaluate_dob_date() RETURNS TRIGGER AS
$$
BEGIN
    NEW.dob_parsed = CAST(REPLACE(NEW.dob_entered,'##','01') AS DATE);
    RETURN new;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER parse_dob 
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
EXECUTE PROCEDURE evaluate_dob_date();

这意味着，您可以将原始输入的数据原封不动地存储起来进行验证，同时在数据库中仍有一个日期字段适合排序和比较等。此外，通过扩展evaluate_dob_date()函数，您可以在发现不同情况时进行匹配，同时仍能拒绝真正无效的记录。

Postgresql触发

Answer 2

这里有两个选项

1. 从数据库中获取数据后，替换服务器端脚本中出现的所有#，然后比较日期。（您也可以动态替换表本身中的数据，而无需使用查询中的if条件修改现有数据）
2. 忽略带有#的日期。这样，您可以只比较有效日期

Answer 3

如果需要保留#字符，我看到的唯一机会就是将其导入varchar列。

如果您绝对需要将信息作为日期，则可以使用一个进行转换的视图，该视图只选择列中没有#的行。

类似的东西

SELECT to_date(dob,'YYYY-MM-DD') as dob_date
FROM your_table
WHERE substr(dob,6,2) <> '##';

如果你经常这样做，你可能需要考虑在该表达式上建立一个索引来加快选择：

CREATE INDEX dob_check ON your_table( substr(dob,6,2) );

请注意，选择中的表达式必须与索引中的表达式完全匹配，才能被查询规划器使用。

如果你想在检索过程中将数据"转换"为有效日期，你可以这样做：

SELECT case 
         case when substr(dob,6,2) = '##' then to_date(substr(dob,1,5)||'01-01', 'YYYY-MM-DD')
         else to_date(dob,'YYYY-MM-DD')
       end as dob_date
FROM your_table;