In my case scenario, I have to migrate a database from MySQL to Postgres, for our backend whose schema is managed by a liquibase script (this is a spring/hibernate project).
The schema is created by Liquibase, so I'm only moving the data.
The problem is that the default casting rules convert MySQL column type TIMESTAMP to Postgres type TIMESTAMPTZ, but in the schema the type is TIMESTAMP (no timezone)
PGLoader generates the following warning:
WARNING Source column "public"."table"."create_date" is casted to type "timestamptz" which is not the same as "timestamp without time zone", the type of current target database column "public"."table"."create_date".
I first tried to overload that default with this CAST in my.load:
CAST type timestamp when default "0000-00-00 00:00:00" and not null
to timestamp drop not null drop default
using zero-dates-to-null
But it is invalid (fails to parse, I tried to understand why but I cant read lisp)
ESRAP-PARSE-ERROR: At
CAST
type timestamp when default "0000-00-00 00:00:00" and not null
^ (Line 28, Column 53, Position 1106)
In context COMMAND:
While parsing COMMAND. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the character , (COMMA)
or the string "--"
or the string "/*"
or the character ; (SEMICOLON)
or the string "after"
or the string "alter"
or the string "before"
or the string "cast"
or the string "decoding"
or the string "distribute"
or the string "drop"
or the string "excluding"
or the string "including"
or the string "keep"
or the string "materialize"
or the string "set"
or the string "to"
or the string "using"
or the string "when"
or the string "with"
An unhandled error condition has been signalled: At
[...] same output 2 more times
My questions are:
- Is it a problem? this is just a warning so as long as PGLoader does not transform the data, it's OK for me
- How can I override that default CAST?
Thanks!
P.S.: minimal my.load to reproduce:
LOAD DATABASE
FROM mysql://user:password@localhost/database
INTO postgresql://user:password@localhost:5432/database
CAST type timestamp when default "0000-00-00 00:00:00" and not null
to timestamp drop not null drop default
using zero-dates-to-null
;