PostgreSQL CAST() giving unexpected results

castfloating pointpostgresqlpostgresql-9.3

I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:

Round up if last digit is 5 or higher.
Round to even if last digit is 5.
Randomly round up or down if last digit is 5.

In fact, whatever logic it is using is definitely 1) deterministic, and 2) not [completely] based on the last digit being 5.

Some example inputs and output of:

CAST ('input' AS DOUBLE PRECISION);

Here, 'input' is not a variable, just a placeholder in this post for a literal numeric value with quotes around it, e.g. below in input column.

input                 output
34.55555555555595     34.5555555555559    *Obviously not rounding up
34.555555555555951    34.5555555555559    *Why not round up here?
34.5555555555559514   34.5555555555559    *Another unexpected round down
34.5555555555559515   34.555555555556     *Unexpected/seemingly inconsistent
34.55555555555595145  34.5555555555559
34.55555555555595146  34.5555555555559
34.55555555555595148  34.5555555555559
34.55555555555595149  34.555555555556     *Huh!?

Version: PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Best Answer

Based on your last comment,

JavaScript parseFloat("34.55555555555595149").toPrecision(15)

the cast should be to the numeric datatype

CAST ('input' AS numeric(17,15))

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL

Related Solutions

Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

Thanks to Hannah Vernon for pointing out the obvious truncation going on, which I'm quite annoyed with myself for not noticing.

Having re run all the queries above in psql, they return the full, non-truncated numbers. Re-running them again in the pgAdmin sql window, I get truncated values. What is most strange is that there is different precision in the truncation and it is reliably so. Who am I to guess at the logic, I'm sure it's a bug and I will look to see if there is a bug tracker for the software.

Many thanks to all for your comments.

EDIT

Here is a comment about column truncation in the FAQ, however it doesn't go into any detail. I would assume this is for long text columns rather than integers over 5 digits, and I can't see where the option it refers to are, but I thought it prudent to include it anyway:

Query tool columns truncated

Some columns are truncated when running a query in the query tool.

You can increase the query option "max. chars per column" for this. Please note that there's another limit for this imposed by the underlying windows control, which apparently doesn't allow more than 511 characters. In pgAdmin V1.1 and up, we provide the function "execute to file", which has no column restrictions. [AP]

http://www.pgadmin.org/pgadmin3/faq/#ColTrunc

Postgresql – Unexpected results with soundex() function when combined st_distance()

The condition:

AND    A.name = B.name

only allows identical names, while you obviously want to match names with the same soundex value. So make that:

AND    soundex(A.name) = soundex(B.name)

If gid is the primary key, you can also simplify to:

GROUP BY a.gid, b.gid

However, since there obviously is no aggregation at all, you can simply remove the GROUP BY clause.

As suggested by PostGis, consider ST_DWithin() instead of ST_Distance() in the JOIN clause. The latter cannot utilize a spatial index.

Related Question