PostgreSQL query not working as intended

Question

I have created a view which has a simple query

create or replace view temp(EFTSLOAD, UOC, CODE) as

select eftsload, uoc,code from subjects where

cast (eftsload as numeric(5,3)) != cast((uoc/48) as numeric(5,3));

But my query select * from temp; gives me rows like

eftsload | uoc | code

0.125 6 ECONXXXX

0.5 24 HISTXXXX

The condition says eftsload != uoc/48 but I get rows where efts = 0.125 and uoc =6 which a violation as 6/48=0.125 and many other instances where the relation eftsload != uoc/48 is clearly not true

Why does this happen?

I can't read that text... Formatted text please, not images. — jarlh
– jarlh, Commented May 2, 2017 at 7:39
Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented May 2, 2017 at 7:40
did not know that I couldnt use images Thanks. I hope this is suficient to understand my question — John Keaton
– John Keaton, Commented May 2, 2017 at 7:52

Tim Biegeleisen · Accepted Answer · 2017-05-02 07:49:56Z

2

One possibility is that the quantity uoc/48 is being treated as integer division, and then afterwards the truncated quotient is being cast to a numeric floating point. But you really want to do floating point division here.

Try this:

select eftsload, uoc, code
from subjects
where cast(eftsload as numeric(5,3)) != uoc / 48::numeric(5,3)

answered May 2, 2017 at 7:49

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

John Keaton Over a year ago

That's it. Its not giving me those values anymore. Thanks!

Collectives™ on Stack Overflow

PostgreSQL query not working as intended

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related