如何在 pgloader 脚本中强制转换后为列定义默认值?

分享于2022年07月17日 database-migration mysql postgresql 问答
【问题标题】:如何在 pgloader 脚本中强制转换后为列定义默认值?(How to define a default value for a column after a cast in a pgloader script?)
【发布时间】:2022-04-28 18:23:11
【问题描述】:

假设我正在使用 pgloader 将 MySQL 数据库迁移到 PostgreSQL,使用 official website 上的默认示例:

load database  
     from      mysql://root@localhost/sakila  
     into postgresql:///sakila  

 WITH include drop, create tables, no truncate,  
      create indexes, reset sequences, foreign keys  

  SET maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila'  

 CAST type datetime to timestamptz  
                drop default drop not null using zero-dates-to-null,  
      type date drop not null drop default using zero-dates-to-null  

 MATERIALIZE VIEWS film_list, staff_list  

 -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'  
 -- EXCLUDING TABLE NAMES MATCHING ~  

 BEFORE LOAD DO  
 $$ create schema if not exists sakila; $$;

我们还假设在表 'foo' 中,我有一个 DATETIME 类型的列 'bar',并且我想在迁移时设置一个新的默认值 '2015-01-01 00:00:00'它到 PostgreSQL(MySQL 中的旧默认值是 '0000-00-00 00:00:00')

如何在 pgloader 迁移脚本中定义它?我在 CAST 关键字之后尝试了以下行,但它在这里抛出了一个错误:

column foo.bar to timestamp set default "2015-01-01 00:00:00",
                                        ^ (Could not parse WHITESPACE)

我在 reference docs 中找不到任何关于默认值的信息。

  • 如果唯一的问题是空格,试试 set default '2015-01-01'
  • 不行,还是不行。我怀疑整个 set default 声明必须使用不同的语法,但我不知道它是什么。
  • 所以在另一个部分尝试: after load do $$ alter table foo alter bar set default '2015-01-01' $$ (我不使用 pgloader,只是阅读手册)。
  • 我知道我可以在加载架构后使用常规查询来做到这一点。我只是想避免在那里这样做,因为我在数据库中有大约 80 个表进行迁移,需要修改这个默认值,并且希望有一种比列出每个表更简单的方法:)
  • 好的。如果要为所有表(在数据库或模式中)中的所有时间戳列设置相同的默认值,可以使用 postgres do 语句来完成。

【解决方案1】:

CAST type datetime2 to "timestamp without time zone DEFAULT NOW()" drop default

  • 您的答案可以通过额外的支持信息得到改进。请 edit 添加更多详细信息,例如引用或文档,以便其他人可以确认您的答案是正确的。你可以找到更多关于如何写好答案的信息 in the help center