Fatal error on SQLite to PostgreSQL migration

[superDuperCyberTechno]

Using v. 3.5.2 (from the official Ubuntu repos) I tried migrating a SQLite database into PostgreSQL:

pgloader database.sqlite postgresql:///test

The SQLite DB has been populated by the PHP framework Laravel.

pgloader returned the following error:

FATAL error: At

  double precision
         ^ (Line 1, Column 7, Position 7)

In context SQLITE-TYPE-NAME:

While parsing SQLITE-TYPE-NAME. Expected:

     the character Space
  or the character ( (LEFT_PARENTHESIS)
  or the string "auto_increment"
  or the string "native"
  or the string "nocase"
  or the string "short"
  or the string "unsigned"
  or the string "varying"
An unhandled error condition has been signalled: At

  double precision
         ^ (Line 1, Column 7, Position 7)

In context SQLITE-TYPE-NAME:

While parsing SQLITE-TYPE-NAME. Expected:

     the character Space
  or the character ( (LEFT_PARENTHESIS)
  or the string "auto_increment"
  or the string "native"
  or the string "nocase"
  or the string "short"
  or the string "unsigned"
  or the string "varying"




What I am doing here?

At

  double precision
         ^ (Line 1, Column 7, Position 7)

In context SQLITE-TYPE-NAME:

While parsing SQLITE-TYPE-NAME. Expected:

     the character Space
  or the character ( (LEFT_PARENTHESIS)
  or the string "auto_increment"
  or the string "native"
  or the string "nocase"
  or the string "short"
  or the string "unsigned"
  or the string "varying"

[dimitri]

I think it's been fixed in a9afddf ; please try pgloader 3.6.1.

[sjdrc]

I still have this exact issue when building from latest master:

postgres@mars:~$ pgloader input.sqlite postgresql:///newdb
2020-03-01T14:27:29.023000+11:00 LOG pgloader version "3.6.30376b2"
2020-03-01T14:27:29.037000+11:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///var/lib/postgresql/input.sqlite {1005AC0783}>
2020-03-01T14:27:29.037000+11:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@UNIX:5432/newdb {1005D0E263}>
2020-03-01T14:27:29.056000+11:00 ERROR sqlite: At

  double precision
         ^ (Line 1, Column 7, Position 7)

In context SQLITE-TYPE-NAME:

While parsing SQLITE-TYPE-NAME. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character ( (LEFT_PARENTHESIS)
  or the string "--"
  or the string "/*"
  or the string "auto_increment"
  or the string "native"
  or the string "nocase"
  or the string "short"
  or the string "unsigned"
  or the string "varying"
2020-03-01T14:27:29.056000+11:00 LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

I have tried to set up custom cast rules, but they don't seem to work with the space in the name.

[dimitri]

Have you tried to "double quote" the data types with space in the name?

[sjdrc]

Using this import configuration:

postgres@mars:~$ cat import.conf 
LOAD DATABASE
     FROM sqlite:///var/lib/postgresql/input.sqlite
     INTO postgresql:///newdb

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

SET work_mem to '16MB', maintenance_work_mem to '512 MB'

CAST type "double precision" to "double precision";

Gives me the following error:

postgres@mars:~$ pgloader import.conf 
KABOOM!
2020-03-02T10:33:11.029000+11:00 LOG pgloader version "3.6.30376b2"
FATAL error: At

  
  CAST type "double precision" to "d
            ^ (Line 9, Column 10, Position 232)

In context CAST-SOURCE-TYPE:

While parsing CAST-SOURCE-TYPE. Problem:

  The production
  
    #\"
  
  does not satisfy the predicate ALPHA-CHAR-P.

Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character ' (APOSTROPHE)
  or the string "--"
  or the string "/*"
  or the character _ (LOW_LINE)
  or any character satisfying ALPHA-CHAR-P
An unhandled error condition has been signalled: At

  
  CAST type "double precision" to "d
            ^ (Line 9, Column 10, Position 232)

In context CAST-SOURCE-TYPE:

While parsing CAST-SOURCE-TYPE. Problem:

  The production
  
    #\"
  
  does not satisfy the predicate ALPHA-CHAR-P.

Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character ' (APOSTROPHE)
  or the string "--"
  or the string "/*"
  or the character _ (LOW_LINE)
  or any character satisfying ALPHA-CHAR-P




What I am doing here?

At

  
  CAST type "double precision" to "d
            ^ (Line 9, Column 10, Position 232)

In context CAST-SOURCE-TYPE:

While parsing CAST-SOURCE-TYPE. Problem:

  The production
  
    #\"
  
  does not satisfy the predicate ALPHA-CHAR-P.

Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character ' (APOSTROPHE)
  or the string "--"
  or the string "/*"
  or the character _ (LOW_LINE)
  or any character satisfying ALPHA-CHAR-P

I have found it difficult to find examples or documentation on how exactly to specify custom casts like this, so I may be doing it wrong. I have also tried:

postgres@mars:~$ pgloader --cast 'type "double precision" to "double precision"' input.sqlite postgres:///newdb

but it only returns the usage information.

[sjdrc]

Hi @dimitri, I'm pretty stuck with this. Is there anything else I can provide to help debug this issue?

[Harleqin]

Maybe your input schema and some example data, preferably pruned down to the minimum that shows the problem, and of course cleaned from sensitive data?

I'm not sure where that double precision thing comes from, but I can see that we currently do not parse it as a valid sqlite-type-name. The SQLite docs seem a bit hand-wavey about allowed types, at first glance.

[Harleqin]

As for quoting: according to our sources, you'd need to use single quotes here, at least to get it parsed. I don't know whether this helps in the end, though.

(Edit) The change mentioned by @dimitri is about allowing the target type to be double quoted, not the source type.

[dimitri]

I think this has now been solved in 0daace9. Closing, please re-open if needed.

[timruffles]

@dimitri appears this is still occurring. To clarify, this is when the sqlite source has type double precision (which does appear to be a valid if uncommon sqlite type name with affinity REAL):

> pgloader --version
pgloader version "3.6.2"
compiled with SBCL 2.0.2
> pgloader bin/convert
2020-05-01T16:59:01.007000+01:00 LOG pgloader version "3.6.2"
KABOOM!
FATAL error: At

  double precision
         ^ (Line 1, Column 7, Position 7)

In context SQLITE-TYPE-NAME:

While parsing SQLITE-TYPE-NAME. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character ( (LEFT_PARENTHESIS)
  or the string "--"
  or the string "/*"
  or the string "auto_increment"
  or the string "native"
  or the string "nocase"
  or the string "short"
  or the string "unsigned"
  or the string "varying"

using following command file (same error if I remove the CAST line):

load database
     from sqlite:///db.sqlite
     into postgresql:///some_db

 WITH include drop, create tables, create indexes

 CAST type "double precision" to "double precision" using float-to-string
 ;

[timruffles]

@dimitri thanks for this tool btw - I did a few table copies to manually convert over the double precision columns and:

      Total import time          ✓   23408688     1.4 GB       3m28.810s

Magic! 👏

[dimitri]

Thanks for your precision about the name coming from the SQLite catalogs themselves, my previous attempts at fixing the bug did not take that into account and only fixed the parsing of the user provided casting rules. It should now be properly fixed. I added a test case in the sqlite sample database too.

If that still does not work, please feel free to open this issue again.