Check if a value can be typecast as an interval in postgres

Question

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:

CASE
    WHEN my_interval [can be cast as interval] THEN my_interval::interval
    ELSE NULL
END as my_valid_interval

Answer 1

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 ...