pgloader icon indicating copy to clipboard operation
pgloader copied to clipboard

Cast not working as expected

Open jtorral opened this issue 2 years ago • 0 comments

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

jtorral avatar Jun 14 '23 21:06 jtorral