I have a user defined value which is entered into a postgres database. If the value has been entered correctly it should be able to be cast as an interval however, some values may have been entered incorrectly which will throw an exception.
Is there a way to check if a value can be cast as an interval? Something along the lines of the following:
WHEN my_interval [can be cast as interval] THEN my_interval::interval
END as my_valid_interval
There is a fairly wide variety of formats that produce valid interval input. The simple answer is therefore that you cannot simply check if some string is a valid interval.
Probably the most straightforward solution is to use a PL/pgSQL function that converts the string to
interval and returns null on any error:
CREATE FUNCTION string_to_interval(s text) RETURNS interval AS $$ BEGIN RETURN s::interval; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$ LANGUAGE plpgsql STRICT;
Use it like:
SELECT string_to_interval(my_interval) AS my_valid_interval FROM ...