0

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:

  1. Is it a problem? this is just a warning so as long as PGLoader does not transform the data, it's OK for me
  2. 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
;
lrkwz
  • 6,105
  • 3
  • 36
  • 59
Baylej
  • 53
  • 7

0 Answers0