postgresql coalesce

Question

Can anyone say why this select returns 3.0 instead of 3.5:

SELECT coalesce(1.0*(7/2),0) as foo

This one returns 3:

SELECT coalesce(7/2,0) as foo

I'm just trying to divide a SUM by COUNT to find an average. I need it 0 if null and 1 rounded by 1 decimal in case I have results.

not having access to postgres, but try coalesce(7/2,0.0) and see if that changes anything. sneaky suspicion it might be imposing int output since 0 is an int. — Marc B
– Marc B, Commented Aug 23, 2012 at 20:20

clee · Accepted Answer · 2012-08-23 20:28:59Z

5

You need to do something like:

SELECT COALESCE((1.0 * 7)/2, 0) AS foo;

answered Aug 23, 2012 at 20:28

clee

11.2k6 gold badges38 silver badges28 bronze badges

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

Comments

score 4 · Accepted Answer · 2012-08-23 20:25:56Z

4

Well, 7/2 is an integer, 1.0 is numeric, and so 1.0*(7/2)==1.0 * 3 == 3.0.

edited Aug 23, 2012 at 20:25

answered Aug 23, 2012 at 20:20

user554546

4 Comments

Gigi Contra Over a year ago

sooo...how do I get 3.5 out of it I'm still unsure.

Gigi Contra Over a year ago

Would you submit that query result to your boss? :) You just reproduced 3.0 instead of 3.5 which is the right result. I read that division (integer division truncates the result) but how do you get a right division result in this situation? Jack please submit your answer. It's the right one.

user554546 Over a year ago

0_o What do you mean by "the right result"? PostgreSQL has integer division. Other languages don't. If you need floating point division, cast appropriately.

user554546 Over a year ago

@GigiContra - And just what do you mean by "would you submit that query result to your boss"?

Chloe · Accepted Answer · 2014-01-05 02:35:24Z

0

This feels better:

select 7.0/2.0; -------------------- 3.5000000000000000

answered Jan 5, 2014 at 2:35

Chloe

26.5k45 gold badges211 silver badges380 bronze badges

Collectives™ on Stack Overflow

postgresql coalesce

3 Answers 3

Comments

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Related