'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 |