[JoelJacobson]

Can someone explain why float is "better" than numeric in this example?

pg1:joel=#* select 1/7::numeric7; ?column? ------------------------ 0.99999999999999999998 (1 row)

pg1:joel=# select 1/7::float*7; ?column? ---------- 1 (1 row)

[mikeash]

It looks like numeric is a decimal type. 1/7 can't be represented exactly in either binary or decimal, so it's going to come down to rounding. It just so happens that 1/7 in binary, rounded to float precision, then multiplied by 7, is equal to 1. Do the same in decimal with whatever precision numeric gives you, and the result is not 1. I don't think there's any deep reason for it, it's just how it happens to work out. You can probably find a value where the opposite is true.

[luhn]

Floating point math is a bit fuzzy, so many languages will round to the nearest integer if the float is within a certain margin.

Numeric types are meant to be exact, so they will be represented as an exact value.

[tmyklebu]

Floating-point math is carefully-defined. There are multiple independently-developed but interoperable implementations and an IEEE standard that talks in detail about how floating-point math is supposed to work. It's not "a bit fuzzy."

[ghettoimp]

It most certainly is a bit fuzzy. For a fun critique of the newer IEEE standard by a real FP expert, see:

http://www.russinoff.com/papers/ieee.pdf

For something more concrete, consider Section 8, "Variations Allowed by the IEEE Floating-Point Standard", of the TestFloat tool for testing floating point implementations for IEEE compliance:

http://www.jhauser.us/arithmetic/TestFloat-3c/doc/TestFloat-...

And of course, many arithmetic operations (e.g., trig functions) aren't even covered by the standard, which occasionally provokes consternation like this...

https://forums.theregister.co.uk/forum/1/2014/10/10/intel_un...

[simonbyrne]

The problem with the "floating point is fuzzy" comment is that people start treating it as some sort of black box, or as if the results are random somehow. Sure there are a few weird things with floating point status flags, but mostly "fuzziness" is perfectly understandable when you grasp what it is doing (including the usual 0.1 + 0.2 != 0.3).

Also the standard does specify trig functions (§9.2 Recommended correctly rounded functions), but that's one of the optional parts, and as far as I know no one has actually implemented them fully (CRlibm came close, but I don't think their pow function has been fully proven to be correctly rounded, and in any case it isn't widely used).

This is actually a big problem with most standards: a lot of them contain finicky details about which only a very small subset of people care. As far as I know, there still isn't a C compiler that implements all the PRAGMAs specified in the C-1999/C-2011 specs.

[tmyklebu]

I wasn't aware of the ambiguity surrounding the underflow flag noted in your second link. However, the other complaints I'm reading from your links (things not specified by the standard may vary in behaviour; the standard is written in English and could have been written in different or better English) do not impact the semantics of floating-point arithmetic in a material way.

[jnordwick]

> so many languages will round to the nearest integer if the float is within a certain margin

What language does this? It is more likely that it is printing out a truncated form but the number still contains that small difference, such as it will print "1.1" even though 1.1 isn't exactly representable in binary. This comes from the often used Grisu set of algorithms for printing which (in Grisu3) prints the smallest string that can represent the floating point bit pattern.

[luhn]

That behavior was what I was referring to. I only have limited understanding on the topic; it seems my explanation was not quite right. Thanks for correcting me.