pgloader
pgloader copied to clipboard
How to cast MYSQL bigint(20) to Postgres interval?
- [ ] 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?