CODEWITHSUNDEEP
sqlpostgresql
John Keaton
John Keaton

Reputation: 51

PostgreSQL query not working as intended

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?

Upvotes: 0

Views: 67

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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)

Upvotes: 2

Related Questions