Thread: Simple sql question
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
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? Cast one of the values to float first. Then, your result will be as expected. Sean
select cast (col1 as float) / cast (col2 as float) from test;
gives 0.5
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tore Lukashaugen
Sent: Wednesday, October 03, 2007 2:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Simple sql question
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
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
"Tore Lukashaugen" <tore@lukashaugen.freeserve.co.uk> writes:
> 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?
Yup --- division of integers yields an integer result.
If you want a fractional result, cast one or both inputs of the division
to numeric or float.
regards, tom lane
Indeed it does, thanks very much guys!
----- Original Message -----
From: "Wright, George" <George.Wright@infimatic.com>
To: "Tore Lukashaugen" <tore@lukashaugen.freeserve.co.uk>;
<pgsql-novice@postgresql.org>
Sent: Wednesday, October 03, 2007 7:35 PM
Subject: Re: [NOVICE] Simple sql question
select cast (col1 as float) / cast (col2 as float) from test;
gives 0.5
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tore Lukashaugen
Sent: Wednesday, October 03, 2007 2:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Simple sql question
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
On 03.10.2007 20:56 Tore Lukashaugen wrote: > Indeed it does, thanks very much guys! > > ----- Original Message ----- From: "Wright, George" > <George.Wright@infimatic.com> > To: "Tore Lukashaugen" <tore@lukashaugen.freeserve.co.uk>; > <pgsql-novice@postgresql.org> > Sent: Wednesday, October 03, 2007 7:35 PM > Subject: Re: [NOVICE] Simple sql question > > > select cast (col1 as float) / cast (col2 as float) from test; > quick and dirty ... select col1 / (col2+0.0) from test; gives 0.5