Postgresql find values that can be cast to numeric

Question

I am using home assistant for home automation tasks using postgresql (presently v 14.7) as a data storage. When home assistant is logging data, it stores them in a schema states where the state is stored as a varchar. So for example when a temperature sensor reads 18.4 degrees, it is stored as "18.4". I want to use grafana to display historical values, so I have to cast the data

select last_updated_ts, state::float from states where entity_id like ....

This works fine, until I try to fetch some data where the sensor has had some problems and state is stored as "unavailable" or another error message. This will make the entire query fail. At the moment I have added an extra where clause:

select last_updated_ts, state::float from states where state> 'a' and entity_id like ....

This works fine as all non-numeric states (I have seen so far...) are text strings, but I would like to have a more generic approach. Does postgres have any generic functions to check if a varchar can be cast to a float? I know I can write a regexp and I have seen some functions with exception handling, but I would prefer if it is something like is_float() that checks if a varchar can be converted.

(I do know about the differences between float and numeric, for my usage, both types can be used)

Answer 1

You can easily do that from PostgreSQL v16 on, using the data validity checking functions:

SELECT state::double precision
FROM states
WHERE pg_input_is_valid(state, 'double precision');