Re: Simple sql question

Tore Lukashaugen wrote:

>Hello,
>
>I am new to Postgres and have an embarassingly simple question. I am running
>v8.2 on Windows Vista although I don't think that has any bearing on the
>answer.
>
>create table test (col1 int, col2 int)
>insert into test values (1,2);
>select col1/col2 from test....yields 0 not 0.5 - why?
>
>Presumably this is because both col1 and col2 are ints and the output needs
>to be casted somehow? Or is it because PGAdmin's SQL interface does not by
>default show the decimal places of output?
>
>Appreciate your help.
>
>Tore
>
>

It's because they're ints, and integer division rounds down (for
positive integers- I forget what the rule is if one or both integers are
negative).  You can cast one (probably t1) to numeric to get the
fractional part, like:

> bhurt2_dev=# create table test (col1 int, col2 int);
> CREATE TABLE
> bhurt2_dev=# insert into test values (1,2);;
> INSERT 0 1
> bhurt2_dev=# select col1::numeric/col2 from test;
>         ?column?
> ------------------------
>  0.50000000000000000000
> (1 row)
>
> bhurt2_dev=#


Brian