bug: Literal floats execute as wrong type with postgres backend, leading to pyarrow error

[dfacoet]

What happened?

On ibis 12.0.0, with postgres backend, ibis.literal(0.0, type="float64") compiles to a generic numeric type, that is interpreted as Decimal leading to an error when running the query on postgres backend to generate a pyarrow table.

Example:

con = ibis.postgres.connect(
    host="localhost",
    port=5433,
    database="benchmark",
    user="username",
    password="password",
)

table = ibis.memtable({"x": [1.0, 2.0, 3.0]})
# Add a constant column
expr = table.mutate(v=ibis.literal(0.5518, type="double"))
con.to_pyarrow(expr)

results in

ERROR (expected): Could not convert Decimal('0.5518') with type decimal.Decimal: tried to convert to double

The python type is correct:

>>> print(f"ibis type: {ibis.literal(0.5518, type='double').type()}")
ibis type: float64

but it's ignored when compiling the query

>>> ibis.to_sql(expr, dialect="postgres")
SELECT
  "t0"."x",
  0.5518 AS "v"
FROM "ibis_pandas_memtable_vsbvqvbl7vczpadjhphsufg6c4" AS "t0"

Not sure if it's a bug, but to me this behaviour is surprising.

If the constant does not have a fractional part, this workaround works (results in an explicit cast in the query):

expr_ok = table.mutate(v=ibis.literal(0).cast("float64"))
print(ibis.to_sql(expr_ok, dialect="postgres"))
con.to_pyarrow(expr_ok)  # succeeds

SELECT
  "t0"."x",
  CAST(0 AS DOUBLE PRECISION) AS "v"
FROM "ibis_pandas_memtable_swnrqy2xarajbemrrjomh7cbh4" AS "t0"

but the same with 0.0 does not

SELECT
  "t0"."x",
  0.0 AS "v"
FROM "ibis_pandas_memtable_hj364toqwvcfjihseqzgmvqlhe" AS "t0"
Traceback (most recent call last):
  File "/tmp/ibis_literal_float_bug.py", line 35, in <module>
    con.to_pyarrow(expr_ok)  # succeeds
    ~~~~~~~~~~~~~~^^^^^^^^^
  File "/Users/davidefacoetti/Code/frontier-platform/frontier-metrics-query/.venv/lib/python3.13/site-packages/ibis/backends/__init__.py", line 260, in to_pyarrow
    table = pa.Table.from_batches(reader, schema=arrow_schema)
  File "pyarrow/table.pxi", line 5028, in pyarrow.lib.Table.from_batches
  File "pyarrow/ipc.pxi", line 757, in pyarrow.lib.RecordBatchReader.__next__
  File "pyarrow/ipc.pxi", line 791, in pyarrow.lib.RecordBatchReader.read_next_batch
  File "pyarrow/error.pxi", line 89, in pyarrow.lib.check_status
  File "/Users/davidefacoetti/Code/frontier-platform/frontier-metrics-query/.venv/lib/python3.13/site-packages/ibis/backends/postgres/__init__.py", line 739, in _batches
    pa.array(batch, type=struct_type)
    ~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "pyarrow/array.pxi", line 375, in pyarrow.lib.array
  File "pyarrow/array.pxi", line 46, in pyarrow.lib._sequence_to_array
  File "pyarrow/error.pxi", line 155, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow/error.pxi", line 92, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: Could not convert Decimal('0.0') with type decimal.Decimal: tried to convert to double

What version of ibis are you using?

12.0.0

What backend(s) are you using, if any?

postgres, duckdb (works on duckdb)

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct

[dfacoet]

Note if anyone needs this with fractional floats: the workaround with ibis.literal(0).cast("float64") + 0.5518 seems to work