check if type is valid pg type

[Dominik Moritz]

Hi all,

I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table. However, pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8). What is the best way to check whether a type is valid or not?

Thanks,
Dominik

[Josh Kupershmidt]

Post by Dominik Moritz
I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table. However, pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8). What is the best way to check whether a type is valid or not?

You could cast the type name to regtype, e.g.

SELECT 'integer'::regtype, 'float'::regtype;

Josh

[Dominik Moritz]

Hi Josh,

That is exactly what I needed. Thank you very much. However, is there a way to do this without getting an exception if the type does not exist?

Cheers,
Dominik

Post by Josh Kupershmidt

Post by Dominik Moritz
I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table. However, pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8). What is the best way to check whether a type is valid or not?

You could cast the type name to regtype, e.g.
SELECT 'integer'::regtype, 'float'::regtype;
Josh

[Josh Kupershmidt]

Post by Dominik Moritz
That is exactly what I needed. Thank you very much. However, is there a way to do this without getting an exception if the type does not exist?

You could wrap the lookup in a little PL/pgSQL function, something like this:

CREATE OR REPLACE FUNCTION is_valid_type(v_type text)
RETURNS boolean
AS $$
BEGIN
PERFORM v_type::regtype;
RETURN true;
EXCEPTION WHEN undefined_object THEN
RETURN false;
END;
$$ LANGUAGE plpgsql stable;

Josh