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

by

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

More Related Contents:

  1. Auto increment table column
  2. Any downsides of using data type “text” for storing strings?
  3. Split comma separated column data into additional columns
  4. How to concatenate columns in a Postgres SELECT?
  5. Time zone storage in data type “timestamp with time zone”
  6. Change PostgreSQL columns used in views
  7. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  8. Casting NULL type when updating multiple rows
  9. How much disk-space is needed to store a NULL value using postgresql DB?
  10. PostgreSQL ORDER BY issue – natural sort
  11. How to store array or multiple values in one column
  12. Select (retrieve) all records from multiple schemas using Postgres
  13. Select first row in each GROUP BY group?
  14. Escaping keyword-like column names in Postgres
  15. Delete duplicate rows (don’t delete all duplicate)
  16. GROUP BY + CASE statement
  17. Split column into multiple rows in Postgres
  18. Perform this hours of operation query in PostgreSQL
  19. Getting date list in a range in PostgreSQL
  20. Postgres Dynamic Query Function
  21. How do I delete a fixed number of rows with sorting in PostgreSQL?
  22. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  23. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  24. Multiple array_agg() calls in a single query
  25. PostgreSQL does not accept column alias in WHERE clause
  26. How to show row numbers in PostgreSQL query?
  27. PostgreSQL Index on JSON
  28. How do I speed up counting rows in a PostgreSQL table?
  29. pseudo_encrypt() function in plpgsql that takes bigint
  30. How to group timestamps into islands (based on arbitrary gap)?

Leave a Comment