32- or 64-bits float division with PrestoSQL

Question

In Presto SQL, SELECT 1 / 3 returns 0, because / performs integer division.

But SELECT 1.0 / 3 returns 0.3... How can I get 0.3333333333? (i.e., more specifically, 32- or 64-bits precision instead of first decimal truncation?)

Photosphere

Answer 1

You can cast() before dividing.

To get a 64-bit precision:

select cast(1 as double) / 3

To get a 32-bit precision:

select cast(1 as real) / 3

Maseru

Answer 2

I suppose the literal 1.0 is treated as DECIMAL(2, 1). You could use floating point literals instead:

SELECT REAL '1'   / 3 -- '1' is a 32-bit float
SELECT DOUBLE '1' / 3 -- '1' is a 64-bit float
SELECT 1e0        / 3 -- scientific notation implies 64-bit float

Gorgonzola

I realized we don't currently document the literals, so here's the PR: github.com/prestosql/presto/pull/2013 – Irresolvable

@piotr that is great. Revised the answer based on the docs (earlier it was just a guess). – Gorgonzola