Skip site navigation (1) Skip section navigation (2)

Re: Simple sql question

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: Tore Lukashaugen <tore(at)lukashaugen(dot)freeserve(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Simple sql question
Date: 2007-10-03 18:43:43
Message-ID: 4703E2DF.6010409@janestcapital.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2007-10-03 18:44:39
Subject: Re: Simple sql question
Previous:From: Sean DavisDate: 2007-10-03 18:38:24
Subject: Re: Simple sql question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group