'How to define a default value for a column after a cast in a pgloader script?

Let's say I'm migrating a MySQL database to PostgreSQL with pgloader, using the default sample on the 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 ~<ory>  

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

Let's also assume that in the table 'foo', I have a column 'bar' with DATETIME type, and I want to set a new default value of '2015-01-01 00:00:00' when I migrate it to PostgreSQL (the old default value in MySQL is '0000-00-00 00:00:00')

How can I define this in the pgloader migration script? I tried the following line just after the CAST keyword, but it is throwing an error right here:

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

And I couldn't find anything about default values in the reference docs.



Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dmitrey