How to check if a value in a numeric field is an integer?

Question

I have a numeric column in Postgresql with these values:

1.0
3.5
1.5
4.0

I want to write a query which will give me all rows that aren't integer. Meaning I want to get:

3.5
1.5

How do I do that?

Answer 1

You can use modulo division:

CREATE TABLE tab(col DECIMAL(10,2));

INSERT INTO tab(col)
VALUES (1.0),(3.5),(1.5),(4.0);

SELECT *
FROM tab
WHERE col % 1 <> 0;

LiveDemo

Remainder:

In mathematics, the remainder is the amount "left over" after performing some computation.

For example:

10 % 4 = 2      because (FLOOR(10/4)  = 2,  2 * 4 + 2 = 10)
13 % 4 = 1      because (FLOOR(13/4)  = 3,  3 * 4 + 1 = 13)
3.5 % 1 = 0.5   because (FLOOR(3.5/1) = 3, 3 * 1 + 0.5 = 3.5)
4.0 % 1 = 0     because (FLOOR(4.0/1) = 4, 4 * 1 + 0 = 4)

So if number has something else than zeros after decimal point you know that it is not Integer.

Alternatively you could use:

SELECT *
FROM tab
WHERE col <> FLOOR(col);

LiveDemo2