pgloader icon indicating copy to clipboard operation
pgloader copied to clipboard

How to cast MYSQL bigint(20) to Postgres interval?

Open comiconomenclaturist opened this issue 3 years ago • 0 comments

  • [ ] pgloader --version
pgloader version "3.6.2"
compiled with SBCL 2.0.6.debian

pgloader.load:

LOAD DATABASE
     FROM	mysql://pgloader_my:PASSWORD@IP_ADDRESS/DB_NAME
     INTO	postgresql://DB_NAME:PASSWORD@localhost/DB_NAME

WITH include drop, create tables

INCLUDING ONLY TABLE NAMES MATCHING 'events_eventstream'

ALTER SCHEMA 'DB_NAME' RENAME TO 'public'

CAST
	column 'events_eventstream'.'preview' to interval drop typemod;
  • [ ] pgloader output you obtain
2022-05-25T13:36:40.433000Z LOG report summary reset
               table name     errors       rows      bytes      total time
-------------------------  ---------  ---------  ---------  --------------
          fetch meta data          0          3                     0.115s
           Create Schemas          0          0                     0.001s
         Create SQL Types          0          0                     0.006s
            Create tables          0          2                     0.021s
           Set Table OIDs          0          1                     0.007s
-------------------------  ---------  ---------  ---------  --------------
public.events_eventstream          0         40     2.6 kB          0.029s
-------------------------  ---------  ---------  ---------  --------------
  COPY Threads Completion          0          4                     0.023s
   Index Build Completion          0          2                     0.012s
           Create Indexes          0          2                     0.008s
          Reset Sequences          0          1                     0.018s
             Primary Keys          0          1                     0.002s
      Create Foreign Keys          0          0                     0.000s
          Create Triggers          0          0                     0.000s
         Install Comments          0          0                     0.000s
-------------------------  ---------  ---------  ---------  --------------
        Total import time          ✓         40     2.6 kB          0.063s

I am trying to migrate an interval field expressed as bigint(20) in mysql, and cast it to an interval in postgres. The problem is that pgloader is assuming the interval is in seconds, but it is in fact in microseconds.

How can I cast this column and retain the data in the correct format?

comiconomenclaturist avatar May 25 '22 13:05 comiconomenclaturist