-1

I have Mysql DB that contains table 'tariff' with field

start_date DATETIME DEFAULT (current_timestamp() + interval 10 year),

And when I try to migrate database to postgreSQL using pgloader i get error

ERROR Database error 0A000: value "current" for datetime does not supported any more QUERY: CRE ATE TABLE yamalkernel.tariff 2021-10-06T09:51:25.248000+03:00 FATAL Failed to create the schema, see above

I run pgloader this way

$ pgloader scenario.load

$ cat scenario.load

LOAD DATABASE
FROM mysql://user:password@127.0.0.1/database1
INTO postgresql://postgres:postgres@127.0.0.1:5432/database2;
uBaH
  • 169
  • 1
  • 12

2 Answers2

1

You can "tell" pgLoader to convert the problem field ("start_date") of the "tariff" table (in the "CAST" section), for example as follows:

#!/bin/bash

cat <<EOF > file.load
LOAD DATABASE
FROM mysql://user:password@127.0.0.1/database1
INTO postgresql://postgres:postgres@127.0.0.1:5432/database2
CAST column tariff.start_date to "timestamptz DEFAULT now() + interval '10 year'" drop default;
EOF

pgloader -v file.load
Andrey Skryl
  • 26
  • 1
0

there are several errors in that code:

  • use timestamp instead of datetime
  • use current_timestamp instead of current_timestamp()
  • the interval value needs to be enclosed in single quotes

So you need to use:

start_date timestamp DEFAULT current_timestamp + interval '10 year'