Thread: integer instead of 'double precision'?

integer instead of 'double precision'?

From
Henry Drexler
Date:
take any table and run

Query
---------------------
select
1/3
from
storage
limit 1


Result
---------------------
?column?
integer
0


Expected Result
---------------------
?column?
double precision
0.33333...



Question
---------------------
Since there is no column type to begin with as this is a made-up column, shouldn't postgres know it is double precision due to the remainder?

I thought perhaps I could cast it as double precision as noted on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html

though doing the following:

select
float8(1/3)
from
storage
limit 1

results in:

float8
double precision
0


any ideas on how to get this type of a manufactured column (not sure the right term for it) to show the double precision result?

Re: integer instead of 'double precision'?

From
Guillaume Lelarge
Date:
On Fri, 2011-09-09 at 10:42 -0400, Henry Drexler wrote:
> take any table and run
>
> Query
> ---------------------
> select
> 1/3
> from
> storage
> limit 1
>
>
> Result
> ---------------------
> ?column?
> integer
> 0
>
>
> Expected Result
> ---------------------
> ?column?
> double precision
> 0.33333...
>
>
>
> Question
> ---------------------
> Since there is no column type to begin with as this is a made-up column,
> shouldn't postgres know it is double precision due to the remainder?
>

You divide an integer with an integer, that should give you an integer.
And that's exactly what it does.

> I thought perhaps I could cast it as double precision as noted on
> http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
>
> though doing the following:
>
> select
> float8(1/3)
> from
> storage
> limit 1
>
> results in:
>
> float8
> double precision
> 0
>

You still divide an integer with an integer. 1/3 as integers has a
result of 0. You then cast it to float which gives you the value 0 in
double precision.

> any ideas on how to get this type of a manufactured column (not sure the
> right term for it) to show the double precision result?

Sure, do select 1./3 from... or select float8(1)/3...


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


Re: integer instead of 'double precision'?

From
Tom Lane
Date:
Henry Drexler <alonup8tb@gmail.com> writes:
> [ "1/3" yields zero ]

Yeah, it's an integer division.

> I thought perhaps I could cast it as double precision as noted on
> http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
> though doing the following:
> float8(1/3)

That's casting the result of the division to float, which is way too
late.  You need to cast one or both inputs to non-integer, for instance

    1.0/3
    1/(3::float8)

etc etc.

            regards, tom lane

Re: integer instead of 'double precision'?

From
Henry Drexler
Date:
thanks Tom and Guillaume,
That sequencing of casting makes sense - I appreciate the clear explanation.


On Fri, Sep 9, 2011 at 11:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Henry Drexler <alonup8tb@gmail.com> writes:
> [ "1/3" yields zero ]

Yeah, it's an integer division.

> I thought perhaps I could cast it as double precision as noted on
> http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
> though doing the following:
> float8(1/3)

That's casting the result of the division to float, which is way too
late.  You need to cast one or both inputs to non-integer, for instance

       1.0/3
       1/(3::float8)

etc etc.

                       regards, tom lane

Re: integer instead of 'double precision'?

From
Henry Drexler
Date:
Perfect, thank you.  I will try to find that in the documentation as I was obviously not looking at the correct page I had linked to earlier.

On Fri, Sep 9, 2011 at 11:05 AM, Day, David <dday@redcom.com> wrote:

Henry,

 

Does this suit your need?

 

select 1/3::float as answer;

      answer      

-------------------

0.333333333333333

(1 row)

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Henry Drexler
Sent: Friday, September 09, 2011 10:42 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] integer instead of 'double precision'?

 

take any table and run

 

Query

---------------------

select

1/3

from

storage

limit 1

 

 

Result

---------------------

?column?

integer

0

 

 

Expected Result

---------------------

?column?

double precision

0.33333...

 

 

 

Question

---------------------

Since there is no column type to begin with as this is a made-up column, shouldn't postgres know it is double precision due to the remainder?

 

I thought perhaps I could cast it as double precision as noted on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html

 

though doing the following:

 

select

float8(1/3)

from

storage

limit 1

 

results in:

 

float8

double precision

0

 

 

any ideas on how to get this type of a manufactured column (not sure the right term for it) to show the double precision result?

 


Re: integer instead of 'double precision'?

From
Scott Ribe
Date:
On Sep 9, 2011, at 8:42 AM, Henry Drexler wrote:

> any ideas on how to get this type of a manufactured column (not sure the right term for it) to show the double
precisionresult? 

Use floating point types in the calculation to begin with.

1.0/3.0
1::float8 / 3::float8
float8(1) / float8(3)
1.0/3
1/3.0
1::float8 / 3
...

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





Re: integer instead of 'double precision'?

From
Willy-Bas Loos
Date:
On Fri, Sep 9, 2011 at 5:09 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
You divide an integer with an integer, that should give you an integer.

Can you tell me the reasoning behind that idea?
Is it a rule that the output type of an operator must equal the input type?
In this case that doesn't seem locigal. I think that the "/" operator should return something that allows fractions, since the operator creates fractions so frequently.
If you should need it to be an integer, e.g. when you update an integer column, casting should  be done just-in-time.

But i don't know much about the internals and the reasoning behind these matters, i would be grateful if you could explain.

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: integer instead of 'double precision'?

From
Chris Angelico
Date:
On Tue, Nov 13, 2012 at 12:16 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
> On Fri, Sep 9, 2011 at 5:09 PM, Guillaume Lelarge <guillaume@lelarge.info>
> wrote:
>>
>> You divide an integer with an integer, that should give you an integer.
>
> Can you tell me the reasoning behind that idea?
> Is it a rule that the output type of an operator must equal the input type?
> In this case that doesn't seem locigal. I think that the "/" operator should
> return something that allows fractions, since the operator creates fractions
> so frequently.

This is an argument that comes up regularly on the Python list, partly
because version 2 had int/int -> int, but version 3 declared that
int/int -> float made more sense.

One of the problems of going to floating point is that it's not a
superset of integers - especially not when your integer type supports
arbitrary precision. It might seem obvious that 7/2 should yield 3.5
and not 3, but what about when the numbers are so large that you lose
precision by going float? Or are there to be some cases where int/int
makes float and some where it makes int? That would be nicely
confusing.

I'm generally happy with either behaviour, as long as its consistent,
and as long as it can be overridden with an explicit type cast when
the other is needed.

ChrisA


Re: integer instead of 'double precision'?

From
Martijn van Oosterhout
Date:
On Mon, Nov 12, 2012 at 02:16:21PM +0100, Willy-Bas Loos wrote:
> On Fri, Sep 9, 2011 at 5:09 PM, Guillaume Lelarge <guillaume@lelarge.info>wrote:
>
> > You divide an integer with an integer, that should give you an integer.
> >
>
> Can you tell me the reasoning behind that idea?
> Is it a rule that the output type of an operator must equal the input type?
> In this case that doesn't seem locigal. I think that the "/" operator
> should return something that allows fractions, since the operator creates
> fractions so frequently.

The thing is, you often do need the version that truncates. It's
supported by the underlying system and if you want a float as output
you can cast one of the arguments to float to do that.  It's been like
this forever (C does it too for example).

For integers it may help if you think of it in combination with the
modulus operator (%).

Python 3 recently changed to give float output by default, but also
provides a // operator to access the truncated version.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

Attachment