pgloader
pgloader copied to clipboard
Cast not working as expected
Trying to convert a mysql 5.7 to Postgres 14
I have the following load file defined
LOAD DATABASE
FROM mysql://******
INTO postgresql://*****
alter schema 'test' rename to 'public'
WITH
include drop, create tables, create indexes, reset sequences,
multiple readers per thread, rows per range = 50000
SET MySQL PARAMETERS
net_read_timeout = '31536000',
net_write_timeout = '31536000',
lock_wait_timeout = '31536000'
CAST
type date drop not null drop default using zero-dates-to-null,
type datetime to timestamp drop default using zero-dates-to-null,
type bigint when (= 20 precision) to bigint drop typemod,
type int when (= 11 precision) to integer drop typemod,
type int when (= 4 precision) to bigint drop typemod,
type year to integer,
type geography to bytea,
type geometry to point using convert-mysql-point,
type point to point using convert-mysql-point
BEFORE LOAD DO
$$ create extension if not exists postgis; $$
;
When I run pgloader against the file, I get the following:
2023-06-14T21:10:15.637000Z ERROR Database error 42601: syntax error at or near "("
QUERY: CREATE TABLE public.ac_assessment_cea
(
assessment_cea_id bigserial not null,
valid_cea int not null default '1',
assessment_number int not null,
project_id int not null,
project_name varchar(45) not null,
cea_number int not null,
cea_size decimal(20,10),
second_most_recent_clearing integer(4),
most_recent_clearing integer(4),
model_point_latitude decimal(10,7),
model_point_longitude decimal(10,7),
longterm_baseline_average_cmass decimal(20,10)
);
2023-06-14T21:10:15.637000Z FATAL Failed to create the schema, see above.
2023-06-14T21:10:15.640000Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
before load 0 1 0.006s
fetch meta data 0 63 0.157s
Create Schemas 0 0 0.001s
Create SQL Types 0 1 0.014s
Create tables 0 0 0.000s
as you can see it is not using a create table with integer, it is still using integer(4) which is making postgres error out
Any clues ?
Thanks
Oh ...
root@carbon1:/var/lib/postgresql# pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.0.1.debian