Thread: How to increase precision?

Hello all,

this is a newbie question:

How to increase the precision of calculations in posgresql?

When I run this query:

select 5/2;

I get:

?column?
--------      2

It should be 2.5 shouldn't it?

Thank you for your time.

Daniel






_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*   
http://join.msn.com/?page=features/junkmail

It's assuming INTs and doing int division. Make one a numeric/real or
explicitly cast:

steve=# select 5/2;?column?
----------       2
(1 row)

steve=# select 5./2;?column?
----------     2.5
(1 row)

Cheers,
Steve



On Friday 02 May 2003 2:38 pm, Katka a Daniel Dunajsky wrote:
> Hello all,
>
> this is a newbie question:
>
> How to increase the precision of calculations in posgresql?
>
> When I run this query:
>
> select 5/2;
>
> I get:
>
> ?column?
> --------
>        2
>
> It should be 2.5 shouldn't it?
>
> Thank you for your time.
>
> Daniel
>
>
>
>
>
>
> _________________________________________________________________
> Help STOP SPAM with the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Hey Katka,

> How to increase the precision of calculations in posgresql?
>
> When I run this query:
>
> select 5/2;
>
> I get:
>
> ?column?
> --------
>        2
>
> It should be 2.5 shouldn't it?

Nope!  You're thinking like a normal person, not like a database engineer.
Translated, here's what you told the database:

Give me INTEGER 5  / INTEGER 2

and the database told you:

INTEGER 2

if you'd asked, the database would also have happily told you:

REMAINDER 1

What you really wanted to ask the database was:

Give me NUMERIC 5  /  NUMERIC 2or in SQL:
SELECT 5::NUMERIC/2::NUMERIC

At which point you will get

2.5000 (NUMERIC)

Look for a general book on SQL databases, which should be able to give you a
primer on data types and casting.

--
-Josh BerkusAglio Database SolutionsSan Francisco

On Fri, 2 May 2003, Katka a Daniel Dunajsky wrote:

> this is a newbie question:
>
> How to increase the precision of calculations in posgresql?
>
> When I run this query:
>
> select 5/2;
>
> I get:
>
> ?column?
> --------
>        2
>
> It should be 2.5 shouldn't it?

It's doing integer division since both arguments are integers. You'll need
to make one of them something like float, double or numeric.

For example, queries like:

select 5./2;
select 5/cast(2 as float);

etc... don't do integer division because one of
the arguments isn't typed as an integer type.