Anyways, I have a question. Im sucessfully porting over numerous sqlite dbs to postgres and pgloader is doing a great job! However, Im running into a couple of errors. One is that that some of my sqlite tables dont have a unique id which messes up the addition of foreign keys into the postgres db. The error:
Database error 42830: there is no unique constraint matching given keys for referenced table "shop"
So I ran this in my load file:
AFTER LOAD DO $$ ALTER TABLE shop$ ADD UNIQUE (id);$$
after load doclause will only run after everything that pgloader does internally, there's no other hooks to run at intermediary stages yet (well, apart from the
alter tablepgloader clauses that impact the catalogs used to migrate)
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.
LOAD DATABASE from ../../data/databases/main_db.sqlite into postgresql://postgres:firstname.lastname@example.org: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';
i habe a little problem, i'm using the 3.1 version an a debian machine with this little script:
LOAD DATABASE FROM mysql://<user>:<password>@localhost/gildamesh_test_db INTO postgres:///gildamesh_test WITH data only, truncate;
and i get this very strange error:
Error on Github
2017-07-03T07:17:33.101000Z ERROR Database error 22P02: invalid input syntax for type numeric: " "
CONTEXT: COPY per, line 1, column timezone: " "