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
