Pgloader: SQLite migration fails to handle auto_increment columns

[dimitri]

Another issue Im running into is using the type int with extra auto_increment to serial. It does not seem to add that modification to the postgres tables from the sqlite3 tables that have autoincrement.
The issue comes from a gitter chat. Please open issues in GitHub even tho for this one I duplicated the content myself, it's only extra work.

@andrewgy8 This is my current load file which doesnt seem to convert auto-incremnt to serial:

LOAD DATABASE
     from ../../data/databases/main_db.sqlite
     into postgresql://postgres:[email protected]:5432/main_db

WITH include drop, 
    create tables, 
    create indexes, 
    reset sequences

CAST type datetime to timestamp drop default drop not null using zero-dates-to-null, 
type int with extra auto_increment to serial keep typemod keep default keep not null 

SET work_mem to '16MB', 
    maintenance_work_mem to '512 MB';

[dimitri]

Please retry with a fresh build, it should work automatically now, without the extra CAST rules you added.

Also I advice against using _timestamp without time zone_, consider reading http://naggum.no/lugm-time.html for why not, and of course PostgreSQL documentation on the topic too, at https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES

[louiz]

I’m running into this issue. It’s weird though, because I have pgloader version "3.4.1", wich is supposed to contain the fix…

My sqlite3 column is id INTEGER PRIMARY KEY AUTOINCREMENT, and it’s converted into id | bigint | not null, and not serial

And that won’t work, even with this command:

pgloader --cast 'type int with extra auto_increment to serial' ./test.sqlite postgresql:///test