All rational numbers are parsed as Decimals.Decimal instead of Float about libpq.jl HOT 3 CLOSED

Only columns with type numeric/decimal will appear this way; float4/real and float8/double precision will not.

The numeric type is an arbitrary-precision number that cannot be represented fully in a Float64. However, Decimals.Decimal is not a great type, and a replacement (or improvements to that package) would be welcome.

You can either force the type in Julia:

julia> execute(conn, sql; type_map=Dict(:numeric => Float64)).column_types
3-element Vector{Type}:
 Float64
 Float64
 Float64

or in SQL:

julia> sql="""
       select avg(1.0)::float8 as average, 1.0::float8 as float_number, 0.0000000001::float8 as really_small_number;
       """
"select avg(1.0)::float8 as average, 1.0::float8 as float_number, 0.0000000001::float8 as really_small_number;\n"

julia> execute(conn, sql).column_types
3-element Vector{Type}:
 Float64
 Float64
 Float64

from libpq.jl.

Thank you for the explanation, @iamed2!

I've been eyeing type_map in the docs to force the conversion, so the example is much appreciated.

I'll close the issue as LibPQ works as expected.

Some additional background:
I've used a standard pattern of download SQL -> DataFrame -> save to .arrow for persistence/version control.
Upon opening, my saved data was rubbish.
It turns out that Arrow.write(...;compress=:lz4) didn't know what to do with Decimals type and saved it however without any warning (an issue opened here).

As for the python side, you're right.
I've just checked that both psycopg2 and sqlalchemy (using the former as the driver) return decimals.
The challenge is that I (and perhaps others) use Pandas to download data (pd.read_sql_query), which forces the Float type, so I will have never seen the decimal type come out of a SQL / I didn't know it's the expected behaviour.

Good learning!

from libpq.jl.

psycopg2 in Python does the same thing, using the decimal module in Python's standard library.

https://www.psycopg.org/docs/faq.html#faq-float

from libpq.jl.