ERROR Database error 42704: type "number" does not exist while migrating from sqlite

[pras-harness]

Getting ERROR Database error 42704: type "number" does not exist while migrating from sqlite

• [ ] pgloader --version

pgloader version "3.6.2"
compiled with SBCL 2.0.11

• [ ] how can I reproduce the bug?

Run below command while migrating from sqlite with a table that has a column of type Number

user$ pgloader /Users/Downloads/data/database.sqlite postgresql:///dronedb
2021-05-09T18:10:20.014850+01:00 LOG pgloader version "3.6.2"

• [ ] pgloader output you obtain

2021-05-09T18:10:20.085160+01:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///Users/Downloads/data/database.sqlite {1004F15443}>
2021-05-09T18:10:20.085292+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://user@UNIX:5432/dronedb {10050617F3}>
2021-05-09T18:10:20.272691+01:00 ERROR Database error 42704: type "number" does not exist
QUERY: CREATE TABLE builds 
(
  build_id            bigserial,
  build_repo_id       bigint,
  build_trigger       text,
  build_number        bigint,
  build_parent        bigint,
  build_status        text,
  build_error         text,
  build_event         text,
  build_action        text,
  build_link          text,
  build_timestamp     bigint,
  build_title         text,
  build_message       text,
  build_before        text,
  build_after         text,
  build_ref           text,
  build_source_repo   text,
  build_source        text,
  build_target        text,
  build_author        text,
  build_author_name   text,
  build_author_email  text,
  build_author_avatar text,
  build_sender        text,
  build_deploy        text,
  build_params        text,
  build_started       bigint,
  build_finished      bigint,
  build_created       bigint,
  build_updated       bigint,
  build_version       bigint,
  build_debug         boolean default '0',
  build_cron          text default '',
  build_deploy_id     number default '0'
);
2021-05-09T18:10:20.290184+01:00 FATAL Failed to create the schema, see above.

Initially the error was
the octet sequence #(128) cannot be decoded.
An unhandled error condition has been signalled: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file /Users/Downloads/data/database.sqlite" {1004E8CA03}>: the octet sequence #(128) cannot be decoded.

• [ ] data that is being loaded, if relevant

2021-05-09T18:10:20.290255+01:00 LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0         57                     0.043s
   Create Schemas          0          0                     0.003s
 Create SQL Types          0          0                     0.019s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

• [ ] How the data is different from what you expected, if relevant
• It seems like as the build_deploy_id is type of number and this type is not present in PostgreSQL, So I have to create a new table with type bigint and than copied the data after renaming the table to get this working, so just checking if this can be handled

[jdamata]

For anyone else that runs into a problem migrating drone sqlite db to postgres:

CREATE TABLE builds1
(
  build_id            bigserial,
  build_repo_id       bigint,
  build_trigger       text,
  build_number        bigint,
  build_parent        bigint,
  build_status        text,
  build_error         text,
  build_event         text,
  build_action        text,
  build_link          text,
  build_timestamp     bigint,
  build_title         text,
  build_message       text,
  build_before        text,
  build_after         text,
  build_ref           text,
  build_source_repo   text,
  build_source        text,
  build_target        text,
  build_author        text,
  build_author_name   text,
  build_author_email  text,
  build_author_avatar text,
  build_sender        text,
  build_deploy        text,
  build_params        text,
  build_started       bigint,
  build_finished      bigint,
  build_created       bigint,
  build_updated       bigint,
  build_version       bigint,
  build_cron          text default '',
  build_deploy_id     bigint default '0'
);

INSERT INTO builds1 SELECT * FROM builds;

drop table builds;

ALTER TABLE `builds1` RENAME TO `builds`

[dhalturin]

it also helps the migration of drone db:

echo '.dump' | sqlite3 YOUR_DRONE_DB.sqlite | sed 's|build_deploy_id NUMBER|build_deploy_id INTEGER|g' | sqlite3 NEW_DRONE_DB.sqlite
docker run -ti -v $(pwd):/tmp/ --rm --name pgloader dimitri/pgloader:latest pgloader /tmp/NEW_DRONE_DB.sqlite postgresql://user:pass@host:5432/drone