使用pgloader从SQLite3迁移至PostgreSQL时遇错误求助

开发者阿泽

2026-6-16

问题根源

报错的核心原因是：pgloader直接将SQLite表中createdat和updatedat列的默认值STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')以字符串常量的形式迁移到PostgreSQL，但PostgreSQL的timestamptz类型默认值需要合法的时间表达式，而非字符串包裹的SQLite专用函数，因此触发invalid input syntax错误。

解决方案

你需要在migracion.load配置中添加规则，把SQLite的默认值替换为PostgreSQL支持的时间表达式，推荐两种方式：

方式1：用`SET DEFAULT`规则直接替换

修改后的配置文件如下，指定目标表的列默认值为PostgreSQL的NOW()函数（返回当前带时区的时间，完全匹配需求）：

LOAD DATABASE
  FROM sqlite://database.sqlite
  INTO postgresql://xxxx:xxxx@localhost:5432/db_marc

WITH include drop, create tables, create indexes, reset sequences

SET role.createdat DEFAULT TO NOW(),
    role.updatedat DEFAULT TO NOW();

方式2：用`BEFORE LOAD DO`块修改默认值

如果有多个表存在相同问题，也可以在表创建完成后批量修改默认值：

LOAD DATABASE
  FROM sqlite://database.sqlite
  INTO postgresql://xxxx:xxxx@localhost:5432/db_marc

WITH include drop, create tables, create indexes, reset sequences;

BEFORE LOAD DO $$
  -- 针对role表修改默认值
  ALTER TABLE role ALTER COLUMN createdat SET DEFAULT NOW();
  ALTER TABLE role ALTER COLUMN updatedat SET DEFAULT NOW();
  
  -- 如果其他表也有同样的createdat/updatedat列，可继续添加
  -- ALTER TABLE other_table ALTER COLUMN createdat SET DEFAULT NOW();
$$;

验证

修改配置后，重新执行命令：