Fetch records that are non zero after the decimal point in PostgreSQL

Question

I have a table with an amount field of type Numeric. It contains different amount values. For example

5.00
7.13
8.86
6.00
1.00

... etc.

I've to fetch only those records that are nonzero after the decimal point. ie, fetch only the records corresponding to the amounts

7.13
8.86

How can I do it?

Solution 1

numeric is exact!

Unlike claimed by another answer, numeric is not a floating-point type, but an arbitrary precision type as defined by the SQL standard. Storage is exact. I quote the manual:

The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required.

Answer

The natural candidate for your question is the function trunc(). It truncates toward zero - basically keeping the integer part while discarding the rest. Fastest in a quick test, but the difference is insubstantial among the top contenders.

SELECT * FROM t WHERE amount <> trunc(amount);

floor() truncates to the next lower integer, which makes a difference with negative numbers:

SELECT * FROM t WHERE amount <> floor(amount);

If your numbers fit into integer / bigint you can also just cast:

SELECT * FROM t WHERE amount <> amount::bigint;

This rounds to full numbers, unlike the above.

Test

Tested with PostgreSQL 9.1.7. Temporary table with 10k numeric numbers with two fractional digits, around 1% have .00.

CREATE TEMP TABLE t(amount) AS
SELECT round((random() * generate_series (1,10000))::numeric, 2);

Correct result in my case: 9890 rows. Best time from 10 runs with EXPLAIN ANALYZE.

Erwin 1

SELECT count(*) FROM t WHERE amount <> trunc(amount)          -- 43.129 ms

mvp 2 / qqx

SELECT count(*) FROM t WHERE amount != round(amount)          -- 43.406 ms

Erwin 3

SELECT count(*) FROM t WHERE amount <> amount::int            -- 43.668 ms

mvp 1

SELECT count(*) FROM t WHERE round(amount,2) != round(amount) -- 44.144 ms

Erwin 4

SELECT count(*) FROM t WHERE amount <> amount::bigint         -- 44.149 ms

Erwin 2

SELECT count(*) FROM t WHERE amount <> floor(amount)          -- 44.918 ms

Nandakumar V

SELECT count(*) FROM t WHERE amount - floor(amount) > .00     -- 46.640 ms

Mostly still true in Postgres 12 (except everything's > 10x faster now). Test with 100k rows instead of 10k:

db<>fiddle here

Solution 2

Will this help

SELECT * FROM table WHERE amount - floor(amount) > .00

Solution 3

This will work:

SELECT *
FROM t
WHERE round(amount,2) != round(amount)

And no, you cannot directly compare floating numbers - below code DOES NOT work (SQLFiddle as proof):

SELECT *
FROM t
WHERE amount != round(amount)

If amount = 1./3 * 3, it looks like it is 1, but it is NOT - comparison will fail.

Solution 4

SELECT *
FROM t
WHERE amount != round(amount);

View more solutions

Comments

[Erwin Brandstetter]

numeric is not a floating point number. Thus your "proof" does not apply.

[mvp]

NUMERIC is the type used in question. My example also uses NUMERIC field, which was calculated as 1./3*3 - this IS possible to happen in real code. However, your example using random() CANNOT generate that corner case - it cannot generate NUMERIC value that is equal to 1./3*3

[mvp]

random() cannot generate value of 1./3*3, which CAN be stored in NUMERIC field, and it is NOT equal to 1

[Erwin Brandstetter]

@mvp: A numeric field cannot hold the exact value 1.0 / 3 to begin with. No existing type can. You are discussing a case that is interesting in itself, but not applicable to the question - as I understand it.