Postgres math expression calculcated for each row in table

Question

Using PostgreSQL, supposing a table like the following:

 12184 |               4 |          83
 12183 |               3 |         171
 12176 |               6 |          95

How can I compute a math expression for each row in the table?

For example, to divide column 2 by column 3, such that the output would be:

 12184 |   0.04819277108
 12183 |   0.01754385965
 12176 |   0.06315789474

My instinct was to try:

SELECT col1, col2 / col3 FROM table_name;

But that return the ceiling (ie. rounded-down) integer part, I need the floating point value.

Vinko Vrsalovic · Accepted Answer · 2009-11-22 23:29:41Z

35

Typical cast trick needed because col2 and col3 are integers (so result is by default an integer)

select col1, col2/col3*1.0 from table

or

select col1, col2/col3::float from table

or (SQL Standard way)

select col1, col2/cast(col3 as float) from table

edited Nov 22, 2009 at 23:29

answered Nov 22, 2009 at 22:47

Vinko Vrsalovic

342k55 gold badges341 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nwb Over a year ago

Thanks very much all. I actually used the following: SELECT col1, cast(col2 as float) / cast(col3 as float) FROM table; Is there any reason to preffer one way opposed to another?

Vinko Vrsalovic Over a year ago

See postgresql.org/docs/8.4/interactive/typeconv.html for details. In fact I prefer your way, as it's standard, the ::type form is a PostgreSQL extension.

Vinko Vrsalovic Over a year ago

And, as you can see from my examples, you can get away casting only the denominator.

Dmitry · Accepted Answer · 2009-11-22 22:30:27Z

2

You can use arithmetic expressions in SELECT clause, like this:

SELECT col1 / col2 AS new_name
FROM t

answered Nov 22, 2009 at 22:30

Dmitry

3,78018 silver badges17 bronze badges

Comments

Paul Tomblin · Accepted Answer · 2009-11-22 22:30:24Z

0

select col1, col2/col3 from table;

Should work. Aren't col2 and col3 numeric?

answered Nov 22, 2009 at 22:30

Paul Tomblin

184k59 gold badges324 silver badges412 bronze badges

1 Comment

nwb Over a year ago

Yes, however I require the floating point value, and Postgres is only returning the integer part.

Szymon Lipiński · Accepted Answer · 2009-11-22 22:48:35Z

0

Try query like this:

SELECT col1, col2 / col3::float FROM table_name;

answered Nov 22, 2009 at 22:48

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

Comments

Ankit Vishwakarma · Accepted Answer · 2017-05-31 11:53:02Z

0

In PgSql the columns are typed. So if you want to operator on them; you need to cast the column.

suppose you have a column 'minutes' and you wanna add '+5' in every values of column 'mintues'

Because you are adding and integer value the minutes column must be a integer only then the addition can be performed.

hence incorrect way:

select *, minutes+5 from my table >> syntax error

select *, minutes::int + 5 from mytable >> give the output

answered May 31, 2017 at 11:53

Ankit Vishwakarma

1,69917 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgres math expression calculcated for each row in table

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related