Migrating an existing sqlite database to postgresql

Question

I'm trying to use pgloader to migrate an existing SQLite database to PostgreSQL. In a table of the database there are some columns of type boolean, since there is no dedicated boolean type in SQLite then these columns are typed as smallint. Following is a part of errors report:

pgloader database.sqlite pgsql:///xman

2024-11-11T06:54:26.108001Z LOG Migrating into #<PGSQL-CONNECTION 
pgsql://user@UNIX:5432/xman {1006007FD3}>
2024-11-11T06:54:26.234002Z ERROR Database error 22P02: invalid input syntax for type smallint: "false"
QUERY: CREATE TABLE foo
(
   id                         bigint,
   created_at                 timestamptz,
   deleted_at                 timestamptz,
   secret                     text default '""',
   name                       text default '""',
   can_reboot                 smallint default 'false',
);
2024-11-11T06:54:26.261002Z FATAL Failed to create the schema, see above.

I've tried to dump the SQLite database into a .sql file then manually modified smallint by boolean but pgloader does not support loading .sql file.

Many thanks for any help.

Answer 1

The error message says:

ERROR Database error 22P02: invalid input syntax for type smallint: "false"

This is due to:

 can_reboot                 smallint default 'false',

'false' is not an integer value, you should simply use default 0 instead.

If the SQL file generated from SQLite was from an integer column, it should contain integers for this column, so it should load without errors.