0

When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values.

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone
enum    | color('Red', 'Green', 'Blue')

Currently, the format_type() call only returns the enum name without values. I believe I need to CASE my select statement against the pg_type and pg_enum tables, but am having trouble with the proper syntax. My attempt is below. Thank you!

SELECT 
    attname,
    CASE
        WHEN format_type(atttypid, atttypmod)::text = ANY(select ARRAY(select t.typname::text from pg_type t join pg_enum e on t.oid = e.enumtypid group by t.typname))
            THEN format_type(atttypid, atttypmod) || '(' || array_to_string(enum_range(NULL::format_type(atttypid, atttypmod)), ',') || ')'
        ELSE format_type(atttypid, atttypmod)
        END AS type
FROM   pg_attribute
WHERE  attrelid = 'myTable'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

1 Answer 1

0

You need a subquery on pg_enum that aggregates the values. This gets easier in your main query if you join to pg_type, so that you can use a CASE expression that uses different paths depending on the column's type

SELECT a.attname, 
       case t.typtype
          when 'e' 
            then format_type(atttypid, atttypmod)||'('||
                  (select string_agg(quote_literal(e.enumlabel),',')
                   from pg_enum e
                   where t.oid = e.enumtypid)||')'
          else format_type(a.atttypid, a.atttypmod)
       end as column_type
FROM   pg_attribute a
  JOIN pg_type t ON a.atttypid = t.oid
WHERE  a.attrelid = 'mytable'::regclass
AND    a.attnum > 0
AND    NOT a.attisdropped
ORDER  BY a.attnum;
1
  • What worked perfectly. Thank you for taking the time. Nov 14, 2022 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.