POSTGRESQL: How to get all possible values for column with type enum array [duplicate]-CodePudding

I've been trying to get all the possible values of a column whose type is an array of enum (ENUM[]) in Postgresql.

The usual method that uses the field data_type from the table information_schema.columns doesn't work as it only returns ARRAY.

CodePudding user response：

The following worked:

SELECT enumlabel
FROM pg_enum
         INNER JOIN pg_type ON enumtypid = typelem
         INNER JOIN information_schema.columns ON typname = udt_name
         INNER JOIN pg_namespace ON pg_namespace.oid = typnamespace
WHERE table_name = 'TABLE_NAME'
  AND column_name = 'COLUMN_NAME'
  AND nspname = 'public'
);

CodePudding user response：

With psql, ypo can simply use \dT `:

\dT  t_enum 
                                       List of data types
 Schema  │  Name  │ Internal name │ Size │ Elements │  Owner  │ Access privileges │ Description 
═════════╪════════╪═══════════════╪══════╪══════════╪═════════╪═══════════════════╪═════════════
 laurenz │ t_enum │ t_enum        │ 4    │ one     ↵│ laurenz │                   │ 
         │        │               │      │ two     ↵│         │                   │ 
         │        │               │      │ three    │         │                   │ 
(1 row)