导入外部数据库数据时将bpchar类型转换为date类型的技术咨询

嗨，我来帮你搞定这个日期转换的问题！你遇到的直接用::date报错的情况，其实是因为PostgreSQL默认的日期解析格式和你的源数据格式（DD.MM.YYYY，比如31.01.2021）不匹配导致的，咱们只需要显式指定日期格式就能解决啦。

下面是具体的操作步骤：

1. 创建匹配源表结构的外部表 #

按照你的需求，外部表需要和源数据库的表结构完全一致，所以date字段还是保留bpchar(10)类型：

CREATE FOREIGN TABLE example_external (
    mouse bpchar(4),
    "date" bpchar(10),
    quantity int4
)
-- 这里记得补充你的外部数据库连接配置，比如SERVER名称、对应的OPTIONS参数，根据你用的外部数据源（比如MySQL、SQL Server等）调整
SERVER your_external_server_name
OPTIONS (...);

2. 创建目标表（带date类型字段） #

这一步和你预期的一致，直接把date字段定义为date类型：

CREATE TABLE example (
    mouse bpchar(4),
    "date" date,
    quantity int4
);

3. 导入数据时显式转换日期格式 #

使用TO_DATE()函数指定源日期的格式串'DD.MM.YYYY'，让PostgreSQL准确解析你的bpchar类型日期：

INSERT INTO example (mouse, "date", quantity)
SELECT 
    mouse,
    TO_DATE("date", 'DD.MM.YYYY') AS "date",
    quantity
FROM example_external;

补充说明：为什么::date会报错？ #

PostgreSQL的datestyle参数默认通常是ISO, MDY或其他组合，它没办法自动识别DD.MM.YYYY这种格式的字符串，所以必须用TO_DATE()显式告知日期的组成顺序，这样转换就不会出错啦。

备注：内容来源于stack exchange，提问作者Daria