Determine if user-defined type is ENUM

As often with such questions, the \set ECHO_HIDDEN on command of psql helps. \dT+ will show the possible values of the enum, if the type in question is an enum. The query behind the output is rather complex, but one can simplify it to fit your needs like

SELECT format_type(t.oid, NULL) AS name,
       array_agg(e.enumlabel ORDER BY e.enumsortorder) AS elements
  FROM pg_type AS t 
  LEFT JOIN pg_enum AS e ON e.enumtypid = t.oid
 WHERE t.typname = 'foo'
 GROUP BY t.oid;

This will return {NULL} (an array with a NULL in it) if the type is not an enum, and the actual elements as an array otherwise. You can tweak it further if necessary.


You can do something like this,

SELECT true
FROM pg_enum
WHERE enumtypid = pg_typeof('You'::foo)::regtype
FETCH FIRST ROW ONLY;

You can create a simple function that does that too,

CREATE FUNCTION is_enum(x regtype)
RETURNS bool
AS $$
  SELECT true
  FROM pg_enum
  WHERE enumtypid = x
  FETCH FIRST ROW ONLY;
$$ LANGUAGE sql
IMMUTABLE;

SELECT is_enum(pg_typeof('You'::foo));
 is_enum 
---------
 t
(1 row)