Postgres query to check a string is a number

Solution 1:

I think the easiest way would be a regular expression match:

select '12.41212' ~ '^[0-9\.]+$'
=> true

select 'Service' ~ '^[0-9\.]+$'
=> false

Solution 2:

I would like to propose another suggestion, since 12a345 returns true by ns16's answer.

SELECT '12.4121' ~ '^\d+(\.\d+)?$'; #true
SELECT 'ServiceS' ~ '^\d+(\.\d+)?$'; #false
SELECT '12a41212' ~ '^\d+(\.\d+)?$'; #false
SELECT '12.4121.' ~ '^\d+(\.\d+)?$'; #false
SELECT '.12.412.' ~ '^\d+(\.\d+)?$'; #false

Solution 3:

I fixed the regular expression that a_horse_with_no_name has suggested.

SELECT '12.41212' ~ '^\d+(\.\d+)?$'; -- true
SELECT 'Service' ~ '^\d+(\.\d+)?$'; -- false