PostgreSQL : How to select values with only two digits after decimal point in a table?

Question

I have table called nums.

CREATE TABLE nums (num numeric);
INSERT INTO  nums SELECT * FROM (VALUES(10.11),(11.122),(12.22),(13.555)) t;

It have following rows

select * from nums

num
--------
10.11
11.122
12.22
13.555

So my question is how to get the values with only two digits after decimal point(.)?

---

Expected Output :-

    num
    --------
    10.11
    12.22

Answer 1

You can try Pattern Matching

SELECT * FROM nums WHERE num::text ~ '(^\d+(\.\d{1,2})?$)';

or Mathematical Functions and Operators - Floor

SELECT * FROM nums WHERE FLOOR (num*100)=num*100

Answer 2

Simply with left() and right():

SELECT * FROM nums WHERE left(right(num::text, 3), 1) = '.';

Or with split_part():

SELECT * FROM nums WHERE length(split_part(num::text, '.', 2)) = 2;

Or take the number modulo 1 and cast to text:

SELECT * FROM nums WHERE length((num%1)::text) = 4

The last one is independent of the character used for comma.

Aside: you can simplify your INSERT statement:

INSERT INTO nums VALUES(10.11),(11.122),(12.22),(13.555);

SQL Fiddle.

Performance

I ran a quick test with a temp table of 100k numbers:

SELECT * FROM nums WHERE  ...

left(right(num::text, 3), 1) = '.';        -- Total runtime: 136 ms
length(split_part(num::text, '.', 2)) = 2; -- Total runtime: 162 ms
length((num%1)::text) = 4;                 -- Total runtime: 179 ms
num::text ~ '(^\d+(\.\d{1,2})?$)';         -- Total runtime: 280 ms (incorrect!)
FLOOR (num*100)=num*100;                   -- Total runtime: 203 ms (incorrect!)

The last two (provided by @varchar) are actually incorrect. See comments and fiddle.