CODEWITHSUNDEEP
postgresqlsqlitepgloader
Juan Esteban Labayen
Juan Esteban Labayen

Reputation: 59

error when migrating data from sqlite3 to postgresql using pgloader

I have this file to do the migration "migracion.load":

LOAD DATABASE
  FROM sqlite://database.sqlite
  INTO postgresql://xxxx:xxxx@localhost:5432/db_marc

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

and when I run "pgloader migracion.load"

I get this error:

    2024-11-20T13:34:09.010631Z LOG pgloader version "3.6.7~devel"
2024-11-20T13:34:09.010631Z LOG Parsing commands from file #P"/mnt/c/Users/Juane/Desktop/fiat 900/migracion.load"
2024-11-20T13:34:09.138197Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///mnt/c/Users/Juane/Desktop/fiat 900/database.sqlite {1006A14453}>
2024-11-20T13:34:09.138197Z LOG Migrating into #<PGSQL-CONNECTION pgsql://juane27@localhost:5432/db_marc {1006A14643}>
2024-11-20T13:34:09.457110Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')"
QUERY: CREATE TABLE role
(
  id        bigserial,
  name      text,
  scope     text,
  createdat timestamptz default 'STRFTIME(''%Y-%m-%d %H:%M:%f'', ''NOW'')',
  updatedat timestamptz default 'STRFTIME(''%Y-%m-%d %H:%M:%f'', ''NOW'')'
);
2024-11-20T13:34:09.457110Z FATAL Failed to create the schema, see above.
2024-11-20T13:34:09.457110Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0        105                     0.085s
   Create Schemas          0          0                     0.000s
 Create SQL Types          0          0                     0.011s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

Do you know what rule I need to add? or what is wrong? I have tried many things.

Upvotes: -1

Views: 48

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22952

It's telling you

invalid input syntax for type timestamp with time zone: "STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')"

That isn't valid syntax for a timestamp is it?

https://www.postgresql.org/docs/current/datatype-datetime.html

I assume the sqlite is storing timestamps as strings. You just need to use CURRENT_TIMESTAMP or now().

Upvotes: 0

Related Questions