PostgreSQL compare two float8 column datatype

Question

I have the following table items. which looks like this

|    date    |  id |   s_px   |   c_px   | fee |
+------------+-----+----------+----------+-----+
| 2015-01-01 | 001 | 5355.00  |  5355.00 |   2 |
| 2015-01-01 | 002 | 13240.00 | 13240.00 |   3 |
| 2015-01-01 | 003 | 5840.00  |  5840.00 |   1 |
| 2015-01-01 | 004 | 20.55    |    20.59 |   5 |
| 2015-01-01 | 005 | 64.42    |    64.42 |   6 |

column s_px and c_px had data type pf float8

when I do the compare s_px and c_px for unequality

select * from items where s_px != c_px

This returns almost the same original table. I know direct float compare is not easy. But is there any thing similar to numpy.isclose

floating-point-gui.de

user330315
– user330315

2018-08-14 19:09:02 +00:00
Commented Aug 14, 2018 at 19:09 — user330315
– user330315, Commented Aug 14, 2018 at 19:09
@a_horse_with_no_name thanks for the link

as - if
– as - if

2018-08-15 22:10:43 +00:00
Commented Aug 15, 2018 at 22:10 — as - if
– as - if, Commented Aug 15, 2018 at 22:10

Gordon Linoff · Accepted Answer · 2018-08-14 18:12:37Z

6

The inequality operator is <> (or !=), so your query is correct. However, floating point values are fickle. I would suggest checking to be sure they are not within some small range of each other:

select *
from items
where abs(s_px - c_px) > 0.0001;

answered Aug 14, 2018 at 18:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL compare two float8 column datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related