Floating point Infinity values in SQLite generate errors loading into Postgres

[zaneselvans]

• [x] pgloader --version

  pgloader version "3.6.2"
  compiled with SBCL 2.0.6.debian
  

• [ ] did you test a fresh compile from the source tree? I did not, tbh.

• [x] did you search for other similar issues? I didn't find any issues that seemed related.

• [x] how can I reproduce the bug? Download a copy of the source SQLite DB Run e.g.

  pgloader pudl.sqlite postgresql:///pudl
  

• [x] pgloader output you obtain

2021-11-28T19:50:59.013000Z LOG pgloader version "3.6.2"
2021-11-28T19:50:59.045000Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/zane/code/catalyst/pudl-work/sqlite/pudl.sqlite {10053F2243}>
2021-11-28T19:50:59.045000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://zane@UNIX:5434/pudl {100554BA43}>
2021-11-28T19:51:07.282000Z ERROR Database error 22P02: invalid input syntax for type double precision: "#.DOUBLE-FLOAT-POSITIVE-INFINITY"
CONTEXT: COPY generation_fuel_nuclear_eia923, line 23446, column fuel_mmbtu_per_unit: "#.DOUBLE-FLOAT-POSITIVE-INFINITY"
2021-11-28T19:51:47.451000Z ERROR Database error 22P02: invalid input syntax for type double precision: "#.DOUBLE-FLOAT-POSITIVE-INFINITY"
CONTEXT: COPY generation_fuel_eia923, line 2503636, column fuel_mmbtu_per_unit: "#.DOUBLE-FLOAT-POSITIVE-INFINITY"

• [x] How the data is different from what you expected, if relevant I may be misunderstanding something, but looking at the documentation it seems like both SQLite and Postgres are able to store +/- Infinity values in floating point numerical columns, and both reference the same IEEE 754 standard, so I would have expected the data to be loaded correctly, using whatever the appropriate translation is between the SQLite and Postgres representations of Infinite values. In this particular case it turns out that these infinite values were bad data (which I've now fixed) but it still seemed like maybe worth reporting.

[AndrewNelis]

I came up against this issue. I don't have time to file a PR yet (though it looks like there hasn't been recent activity on this?) but I can share the code change I made to fix this:

src/utils/transforms.lisp changed:

(defun float-to-string (float)
  "Transform a Common Lisp float value into its string representation as
   accepted by PostgreSQL, that is 100.0 rather than 100.0d0."
  (declare (type (or null fixnum float string) float))
  (when float
    (cond
      ((> float most-positive-double-float) "Infinity")
      ((< float most-negative-double-float) "-Infinity")
      (t (typecase float
                      (double-float (let ((*read-default-float-format* 'double-float))
                                        (princ-to-string float)))
                      (string       float)
                      (t            (princ-to-string float)))))))