The following PostgreSQL examples return only those rows that have numeric values in a given column.

Sample Data

Let’s create a table with sample data:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    c1 varchar(255)
);

INSERT INTO t1 (c1) VALUES 
    ('0'),
    ('1'),
    ('+1'),
    ('-1'),
    ('00.00'),
    ('73.45'),
    ('+73.45'),
    ('-73.45'),
    ('.246'),
    ('-.34e7'),
    ('12.e-3'),
    ('1.2e+4'),
    ('a'),
    ('9afc'),
    ('e7'),
    ('+e0'),
    ('Ten'),
    ('5 Dollars');

SELECT * FROM t1;

The table has now been created and contains the following data:

    c1     
-----------
 0
 1
 +1
 -1
 00.00
 73.45
 +73.45
 -73.45
 .246
 -.34e7
 12.e-3
 1.2e+4
 a
 9afc
 e7
 +e0
 Ten
 5 Dollars

The column is a varchar(255) column, so it’s not numeric. It can (and does) contain numbers but these are stored as character data. It can also contain arbitrary text (which it does).

Return All Numeric Values

We can use the following query to return all numeric values from the above table:

SELECT c1
FROM t1 
WHERE c1 ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

Result:

   c1   
--------
 0
 1
 +1
 -1
 00.00
 73.45
 +73.45
 -73.45
 .246
 -.34e7
 1.2e+4

Return Integers

If we only want to return integers, the query can be a lot simpler:

SELECT c1
FROM t1 
WHERE c1 ~ '^[0-9]+$';

Result:

 c1 
----
 0
 1

Contains Numeric Data

If we want to find rows that contain numeric data (even if they also contain non-numeric data), we can do the following:

SELECT c1 
FROM t1 
WHERE c1 ~ '[0-9]+';

Result:

    c1     
-----------
 0
 1
 +1
 -1
 00.00
 73.45
 +73.45
 -73.45
 .246
 -.34e7
 12.e-3
 1.2e+4
 9afc
 e7
 +e0
 5 Dollars

In PostgreSQL, ~ is a case-sensitive operator for matching the given regular expression. For case-insensitive matches, use ~*.

You can use !~ to return all rows that don’t match the regular expression (and !~* for case-insensitive matches).

POSIX Character Classes

Postgres also supports POSIX character classes. So we can use [:digit:] instead of [0-9] if we prefer.

Example:

SELECT c1
FROM t1 
WHERE c1 ~ '^[[:digit:]]?$';

Result:

 c1 
----
 0
 1