Re: Selecting reals into doubles

On Wed, Mar 03, 2004 at 11:19:15 +0000,
  Will Newton <will@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