Just a quick tutorial for creating and displaying the existing ENUMs (Enumerated Data Types) in PostreSQL.

Creating ENUMs in PostreSQL

Creating an ENUM field in PostreSQL is very easy, we just need to use the CREATE TYPE statement:

    CREATE TYPE [name] AS ENUM ([value1][,value2...]);

For example, if we need to create a table with a field “gender” which only accepts the values male and female, we could do something like this:

    CREATE TYPE gender_enum AS ENUM ('Male', 'Female');
    
    CREATE TABLE person(
      name VARCHAR(100) NOT NULL,
      gender gender_enum NOT NULL
    );

How to display the existing ENUMs

The problem comes when you want to know the already existing ENUMs. Although it’s a bit verbose, this query should do the trick:

select 
    n.nspname as enum_schema,  
    t.typname as enum_name,
    string_agg(e.enumlabel, ', ') as enum_value
from pg_type t 
    join pg_enum e on t.oid = e.enumtypid  
    join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name;