如何定期将MS Access表数据追加至PostgreSQL现有表？含FDW疑问

我来给你梳理几个实用的方案，搞定你遇到的问题——之前用Access的「导出到ODBC」会生成新表，现在要改成定期追加记录，包括你提到的FDW方案的具体操作步骤：

方法一：直接用Access的ODBC追加功能（无需额外工具） #

其实你之前用的导出功能只是默认的「创建新表」模式，Access本身就支持追加到现有表，步骤很简单：

• 打开你的Access数据库，找到要迁移的源表
• 切换到「外部数据」选项卡，点击「ODBC 数据库」
• 在弹出的窗口里，选择**「将记录追加到指定表中」**，然后点确定
• 选择你已经配置好的PostgreSQL ODBC数据源，下一步
• 选中PostgreSQL里的目标现有表，点击确定
• 跟着向导完成字段映射（一定要确保Access和PostgreSQL的字段名、数据类型匹配），最后执行追加就行

如果想自动化这个操作，不用每次手动点，可以写个Access VBA脚本，然后用Windows任务计划定期触发：

Sub AppendAccessDataToPG()
    Dim db As DAO.Database
    Dim appendQuery As DAO.QueryDef
    
    Set db = CurrentDb()
    ' 这里替换成你的PostgreSQL连接信息和表名
    Set appendQuery = db.CreateQueryDef("", _
        "INSERT INTO [ODBC;DRIVER={PostgreSQL Unicode};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=用户名;PWD=密码;PORT=5432].[PG目标表名] " & _
        "SELECT * FROM [Access源表名]")
    
    ' 执行追加，出错会提示
    appendQuery.Execute dbFailOnError
    
    ' 清理资源
    Set appendQuery = Nothing
    Set db = Nothing
    
    MsgBox "数据追加完成！", vbInformation
End Sub

写完脚本后，你可以把它存成宏，然后在Windows任务计划里设置每2个月运行一次这个Access宏就行。

方法二：PostgreSQL FDW方案（更灵活的服务器端操作） #

FDW可以让PostgreSQL直接读取Access的数据，然后用SQL完成追加，全程在PG端操作，步骤如下：

1. 安装ODBC FDW扩展 #

首先得在PostgreSQL服务器上装odbc_fdw这个扩展，它用来连接ODBC数据源（包括Access）：

• 如果是Linux服务器：先装unixodbc和unixodbc-dev依赖，然后编译安装odbc_fdw；
• 如果是Windows服务器：直接从PostgreSQL的扩展库（比如Stack Builder）里安装就行。

然后在PG中启用扩展：

CREATE EXTENSION IF NOT EXISTS odbc_fdw;

2. 创建连接Access的服务器对象 #

这里可以用系统DSN，也可以直接写连接字符串，比如：

CREATE SERVER access_link
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (
    -- 如果你已经在系统里配置了Access的ODBC数据源，就用dsn参数
    -- dsn 'MyAccessDSN',
    -- 不想配置DSN的话，直接写连接字符串（替换成你的Access文件路径）
    connection_string 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\你的路径\Access数据库.accdb;'
);

3. 创建用户映射 #

把PostgreSQL的用户映射到Access（Access如果没密码就留空）：

CREATE USER MAPPING FOR postgres -- 替换成你的PG用户名
SERVER access_link
OPTIONS (
    username '',
    password ''
);

4. 创建外部表（映射Access的源表） #

这个外部表相当于PG里的“虚拟表”，直接指向Access的源表，字段要和Access表一一对应：

CREATE FOREIGN TABLE access_source (
    id INT,
    user_name VARCHAR(50),
    create_time TIMESTAMP
    -- 这里要严格对应Access表的字段名和数据类型，别写错
)
SERVER access_link
OPTIONS (
    table 'Access源表名' -- Access的表名，注意大小写
);

5. 执行追加操作 #

现在就可以用普通的SQL把数据从外部表追加到PG的目标表了，还能加条件避免重复：

INSERT INTO pg_target_table (id, user_name, create_time)
SELECT id, user_name, create_time
FROM access_source
-- 比如只追加最近2个月的数据，防止重复插入
WHERE create_time >= CURRENT_DATE - INTERVAL '2 months';

6. 定期自动化 #

如果想让这个追加操作自动执行，可以用PG的pg_cron扩展做定时任务：
先装pg_cron：

CREATE EXTENSION IF NOT EXISTS pg_cron;

然后创建定时任务（比如每2个月的1号凌晨2点执行）：

SELECT cron.schedule(
    'auto-append-access-to-pg',
    '0 2 1 */2 *', -- 定时规则：分 时 日 月 周，这里是每2个月的1号凌晨2点
    $$
    INSERT INTO pg_target_table (id, user_name, create_time)
    SELECT id, user_name, create_time
    FROM access_source
    WHERE create_time >= CURRENT_DATE - INTERVAL '2 months';
    $$
);

几个关键注意事项 #

• 字段类型匹配：一定要确保Access和PG的字段类型对应，比如Access的「文本」对应PG的VARCHAR，「日期/时间」对应TIMESTAMP，不然会报错；
• 避免重复数据：不管用哪种方法，都要加过滤条件（比如用主键或者时间戳），别把已经迁移过的数据又插一遍；
• 权限问题：操作的用户要有Access的读取权限，以及PG的目标表写入权限，不然会失败。

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