Large bigints loading with invalid input syntax

[nathanclayton]

When attempting to load a large bigint from MS Sql Server it appears that the values are coming across in an exponential notation unreadable by PostgreSQL.

For example - 3580417700000007 comes across as 3.580417700000007d16, which can't be read by PostgreSQL.

[dimitri]

It certainly means that the driver or the SQL query is returning the values as float rather than bigint. I don't currently have a MS SQL test case that I can use (because licensing), will see what I can do about that without a testing env (if anything).

[nathanclayton]

IIRC, the d0 notation is a lisp variant of exponential form, which it probably does when numbers are too large (e.g. 16 digits in this case).

On 5/13/2015 5:02 PM, Dimitri Fontaine wrote:

It certainly means that the driver or the SQL query is returning the values as float rather than bigint. I don't currently have a MS SQL test case that I can use (because licensing), will see what I can do about that without a testing env (if anything).

— Reply to this email directly or view it on GitHub https://github.com/dimitri/pgloader/issues/232#issuecomment-101855181.

[dimitri]

What you say is true, but in Common Lisp you don't get the exponential print for integers (fixnums), only for double precision floats. See http://www.lispworks.com/documentation/HyperSpec/Body/t_short_.htm

CL-USER> (- 3580417700000007)
-3580417700000007
CL-USER> (type-of (- 3580417700000007))
FIXNUM

[nathanclayton]

It looks like it was an error with FreeTDS - the default protocol version (4.2) needs to be changed to 8.0 to support bigint properly or else it gets coerced to a float.

As an FYI - if you're using centos, the file that needs to be edited is /etc/freetds.conf - remove the semi-colon in front of the version and swap it for 8.0. This isn't in the FreeTDS documentation.

[dimitri]

Oh, thanks for updating the issue with the fix details!