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

[EricBLivingston]

• [x] pgloader --version

  pgloader version "3.6.3~devel"
  compiled with SBCL 2.1.11.debian
  

• [x] did you test a fresh compile from the source tree?

  No, I did sudo apt install pgloader in wsl

• [x] did you search for other similar issues?

• [x] how can I reproduce the bug?

LOAD DATABASE FROM sqlite://database.sqlite INTO postgresql://[email protected]:5432/db

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

;

• [x] pgloader output you obtain

2024-12-09T15:29:55.010000Z LOG pgloader version "3.6.3~devel" 2024-12-09T15:29:55.010000Z LOG Parsing commands from file #P"pgloader.conf" 2024-12-09T15:29:55.160009Z LOG Migrating from #<SQLITE-CONNECTION sqlite://database.sqlite {1007FB0483}> 2024-12-09T15:29:55.160009Z LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:5432/db {1007FB0FB3}> 2024-12-09T15:29:55.620034Z 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-12-09T15:29:55.630035Z FATAL Failed to create the schema, see above. 2024-12-09T15:29:55.630035Z LOG report summary reset table name errors rows bytes total time

---

        fetch          0          0                     0.000s

fetch meta data 0 116 0.310s Create Schemas 0 0 0.000s Create SQL Types 0 0 0.010s Create tables 0 0 0.000s

---

---

[nguyentranchung]

When migrating n8n from SQLite to PostgreSQL, I encountered a situation similar to yours.

[mbegerau]

@nguyentranchung I was just migrating n8n from SQLite to PostgreSQL as well and for me it worked casting the datetime type like this:

LOAD DATABASE
	FROM sqlite:///path/to/db.sqlite
	INTO postgresql:///n8n
WITH include drop, quote identifiers, create tables, create indexes, reset sequences
CAST type datetime to "timestamp not null default current_timestamp" drop default;

or alternatively timestamptz

[mbegerau]

@nguyentranchung I was just migrating n8n from SQLite to PostgreSQL as well and for me it worked casting the datetime type like this:

LOAD DATABASE
	FROM sqlite:///path/to/db.sqlite
	INTO postgresql:///n8n
WITH include drop, quote identifiers, create tables, create indexes, reset sequences
CAST type datetime to "timestamp not null default current_timestamp" drop default;

or alternatively timestamptz

Sorry, I know this is not the n8n repo. But I just wanted to comment that the described approach caused some issues later when restarting n8n. So what I ended up doing is create the schema by running an empty n8n once connected to PostgreSQL and then I imported only the data like this:

LOAD DATABASE
	FROM sqlite:///path/to/db.sqlite
	INTO postgresql:///n8n
WITH truncate, data only, quote identifiers
EXCLUDING table names like 'migrations';

This results in a ton of warnings, because pgloader assumes different data types than the ones that already exist, but nonetheless it imports the data and n8n starts up without errors.