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

Question

我有一个名为 nums 的表。

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

它有以下几行

select * from nums

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

所以我的问题是如何获取小数点后只有两位数的值(.)？

---

预期输出:-

    num
    --------
    10.11
    12.22

Answer 1

你可以试试Pattern Matching

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

或Mathematical Functions and Operators - Floor

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