sqlite integer column with reals (invalid syntax "2.32d0")

[akuseru]

I have an sqlite integer column that contains reals. getting these loaded in is proving to be quite challenging.

I have modified the table in postgres and have attempted to load the data in with a cast to a real but it doesn't seem to be formatting the data correctly:

load database
    from 'app_data.sqlite'
    into postgresql://abc:123@127.0.0.1:5432/sales

    with include no drop, create no tables, truncate, create indexes, reset sequences

    cast column item.price to real

    set work_mem to '16MB', maintenance_work_mem to '512 MB';

is there something that i am missing? or do i just need to edit the sqlite file to fix this problem?

[akuseru]

hmm i tried to specify a formatter using float-to-string but it doesn't seem to like 0 values?

ERROR The value 0 is not of type (OR NULL SINGLE-FLOAT DOUBLE-FLOAT (VECTOR CHARACTER) (VECTOR NIL) BASE-STRING).

[dimitri]

I just tried with adding a new table item in my SQLite test case, with a column price of type real, and some values, and have no problem here:

select * from item;
 id | price 
----+-------
  1 |  12.3
  1 |     0
(2 rows)

Can you provide with more information, like --verbose --debug, or a sqlite file from which to reproduce the issue?

[akuseru]

hmm with the smaller table that has just the problem columns inserts are ok.

https://github.com/akuseru/xivsqlite is what i am working with. the sql file is what i used to fix some typos in the schema.

the table in question is Items

[akuseru]

I tried creating a new sqlite file with just the problem tables and insert went fine. leading me to believe that it's something related to the structure of the sqlite file and how the cast is being handled. but I am not sure Lisp is new to me.

[akuseru]

I was able to successfully create the import by creating a new table with the problem columns described as real in sqlite.

[dimitri]

I could copy your repository and reproduce the error locally, will investigate later this week... thanks!

CONTEXT: COPY item, line 30, column autoattack_hq: "4.66d0"

[dimitri]

I managed to import your test-case with the following command, and the previously committed patches that allow using the float-to-string transformation function on integers.

load database
    from 'app_data.sqlite'
    into postgresql:///app_data

    with include drop, create tables, truncate, create indexes, reset sequences

    cast column Achievement.data to jsonb,
         column Item.data to jsonb,
         column Quest.data to jsonb,
         column BNpcName.data to jsonb,
         column ENpcResident.data to jsonb,
         column FCReputation.data to jsonb,
         column Recipe.data to jsonb,
         column InstanceContent.data to jsonb,
         column Gathering.data to jsonb,
         column Item.autoattack to float using float-to-string,
         column Item.autoattack_hq to float using float-to-string,
         column Item.attackinterval to float using float-to-string,
         column Item.attackinterval_hq to float using float-to-string

    set work_mem to '16MB', maintenance_work_mem to '512 MB';

More could be done related to fixing this issue, such as adding details about the transformation failure (column name and function where the failure did happen), and maybe improving the casting system to add some default transformation function for some source/target couples.

That said, the very simple modifications made allow you to import your data...

BTW, as your data cols are all containing json data I did fancy loading them into properly typed field for that in PostgreSQL. Use json if you want to just validate that the text is correct, jsonb if you intend to process the fields in WHERE clauses or projections (selecting just some field within the blob, say). Or maybe normalize those data generic fields...

Have fun with PostgreSQL!

[akuseru]

Sweet!

Thanks a lot for your help and this awesome tool.