I'm looking to query rows where a DOUBLE column is exactly zero but the following query also returns rows where the value is near but not exactly zero.
SELECT * FROM trades
WHERE timestamp in today()
AND trade_price = 0.0
It is returning rows with values such as 1.9e-12 or 4.28e-11. That's almost zero, but it is not. Is there a way to filter for rows that are exactly zero?
A workaround for this might be casting the price to string and compare with “0.0”.
select * from trades where
trade_timestamp in today()
and cast(trade_price as varchar) = '0.0'
Maybe multiplying by a large number would also work, but you would need to be careful if some numbers are very small and some quite large to choose a multiplier that works for you.
select * from trades
where trade_timestamp in today()
and trade_price * 1000000.0 = 0.0