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月11日T06:54:26.108001Z LOG Migrating into #<PGSQL-CONNECTION 
pgsql://user@UNIX:5432/xman {1006007FD3}>
2024年11月11日T06: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月11日T06: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.

• White Owl: If you already have sql file - just use psql. It is a standard Postgresql's tool.

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.

• Ta Thanh Dinh: Thank you. The problem is that in the "needed to migrate" database of SQLite, there are many records containing this can_reboot and the values at this field are either true or false. That is the reason why I've tried to modify smallint to boolean.
• bobflux: If you dump your sqlite database to SQL, manually change the column type to bool in the CREATE TABLE statement, and load it with psql, then postgres will accept 'true' and 'false' values for the column.