PostgreSQL: How to Check if String is a Number

Often in PostgreSQL you may want to check if a string is a number.

You can use the following syntax to do so:

SELECT *,
       (product_label ~ '^\d+(\.\d+)?$') AS string_is_number
FROM products;

This particular example checks if each string in the product_label column is a number and returns either true or false as a result.

We then assign these results to a new column named is_number.

The following example shows how to use this syntax in practice.

Example: How to Check if String is a Number in PostgreSQL

Suppose that we create the following table named products that contains information about various products sold at some retail store:

-- create table named 'products'
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  product_label TEXT,
  date_sold DATE
);

-- add rows to the table 
INSERT INTO products VALUES (0001, '455622', '2024-09-11');
INSERT INTO products VALUES (0002, 'A00933', '2024-09-12');
INSERT INTO products VALUES (0003, '2298.1', '2024-09-10');
INSERT INTO products VALUES (0004, '345.07', '2024-09-15');
INSERT INTO products VALUES (0005, 'CHYYGG', '2024-09-18');
INSERT INTO products VALUES (0006, 'C74359', '2024-09-22');

-- view all rows in table
SELECT * FROM products;

 id | product_label | date_sold  
----+---------------+------------
  1 | 455622        | 2024-09-11
  2 | A00933        | 2024-09-12
  3 | 2298.1        | 2024-09-10
  4 | 345.07        | 2024-09-15
  5 | CHYYGG        | 2024-09-18
  6 | C74359        | 2024-09-22

Suppose that we would like to check if each string in the product_label column is a number.

We can use the following syntax to do so:

SELECT *,
       (product_label ~ '^\d+(\.\d+)?$') AS string_is_number
FROM products;

 id | product_label | date_sold  | is_number 
----+---------------+------------+-----------
  1 | 455622        | 2024-09-11 | t
  2 | A00933        | 2024-09-12 | f
  3 | 2298.1        | 2024-09-10 | t
  4 | 345.07        | 2024-09-15 | t
  5 | CHYYGG        | 2024-09-18 | f
  6 | C74359        | 2024-09-22 | f

Notice that the resulting table has a new column named is_number that contains true (t) and false (f) values to indicate whether the corresponding string in the product_label column is a number or not.

For example, we can see:

• The string 455622 is a number so the is_number column returns true.
• The string A00933 is not a number so the is_number column returns false.
• The string 2298.1 is a number so the is_number column returns true.

And so on.

Feel free to use similar syntax to check if strings in a column of your own table are numbers.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Extract Substring Before Specific Character