469,588 Members | 2,779 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Selecting reals into doubles


I have attached some SQL which produces what to me, at least, is
rather unexpected results. Selecting real columns into double
precision columns loses some precision. Is this expected or documented
anywhere?

Thanks,
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
2 1258
On Wed, Mar 03, 2004 at 11:19:15 +0000,
Will Newton <wi**@gbdirect.co.uk> wrote:

I have attached some SQL which produces what to me, at least, is
rather unexpected results. Selecting real columns into double
precision columns loses some precision. Is this expected or documented
anywhere?
You left out the output. But probably what you are seeing are the effects
of increased precision not decreased precision. Neither of the two
numbers you entered is exactly representable as floating point numbers.
When being printed as single precision numbers you got the same thing
back as you entered because within the number of digits used to display
single precision numbers those are going to be the closest to what is
stored. This isn't going to be the case for double precision numbers
in general.

If you really want exact decimal fractions, you want to use the numeric type
to store them.

Thanks, DROP TABLE precision_test;
DROP TABLE precision_test2;

CREATE TABLE precision_test
(
foo real
);

INSERT INTO precision_test
SELECT 20.20
UNION SELECT 1969.22;

CREATE TABLE precision_test2
(
foo double precision
);

INSERT INTO precision_test2 (foo) SELECT foo from precision_test;
SELECT * FROM precision_test;
SELECT * FROM precision_test2;
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
Will Newton <wi**@gbdirect.co.uk> writes:
I have attached some SQL which produces what to me, at least, is
rather unexpected results. Selecting real columns into double
precision columns loses some precision. Is this expected or documented
anywhere?


You shouldn't be surprised; this is a fundamental behavior of floating
point arithmetic anywhere.

There isn't any "loss of precision" per se --- the value represented in
the float8 column is the same as what was in the float4 column. The
difference is that the float8 output routine is programmed to print
about 15 digits of precision whereas the float4 routine prints no more
than 6. So you get to see the fact that the stored value wasn't really
20.2 but only something close to it.

If you find this surprising maybe you should be using type "numeric".

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Daniel Lidström | last post: by
42 posts views Thread by Dooglo | last post: by
12 posts views Thread by John Smith | last post: by
27 posts views Thread by brad | last post: by
5 posts views Thread by =?Utf-8?B?TWFuanJlZSBHYXJn?= | last post: by
2 posts views Thread by utab | last post: by
48 posts views Thread by Bill Cunningham | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.