2

I am using a loading file with pgloader and there is a particularly problematic table in my MySQL database with malformed datetimes. I want to just skip that column during the migration. How can I do this in a loading file? Something like:

LOAD DATABASE 
    FROM mysql://root@localhost/mydb 
    INTO postgresql:///mypgdb
    INCLUDING ONLY TABLE NAMES MATCHING 
    'Table_with_Datetimes_Column' AS dt,
    'My_Other_Table'
    EXCLUDING dt.Malformed_Column
;
medley56
  • 1,181
  • 1
  • 14
  • 29

1 Answers1

3

You can exclude the problematic table entirely, and then migrate over data from a view, using the MATERIALIZING VIEW clause of the load command. So first define a view on-top of your table wherein you omit the problematic column, then

exclude table name matching 'Table_with_Datetimes_Column'
materialize views 'v_Table_without_Datetimes_Column'

See the examples in the test directory in the repository for pgloader, in particular the sakila.load one: https://github.com/dimitri/pgloader/blob/master/test/sakila.load

Dimitri Fontaine
  • 236
  • 1
  • 6
  • This is very similar to what I'm currently doing. Instead of creating a materialized view, I just drop the columns and load the table normally. Your approach is better in that it preserves the original database and it can all be put in the load file. I'll try it today. – medley56 Aug 15 '17 at 13:10