Postgresql get enum

get enum.sql

-- Fetch enum values

SELECT e.enumlabel
FROM pg_type t
INNER JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = :enumtypname
ORDER BY e.enumsortorder;

-- export enums

SELECT 'CREATE TYPE ' ||t.typname || ' AS ENUM (' || string_agg('''' || e.enumlabel || '''', ', ') || ');' AS sql
FROM pg_type t
INNER JOIN (
    SELECT pge.enumtypid, pge.enumlabel
    FROM pg_enum pge
    ORDER BY pge.enumtypid, pge.enumsortorder
) e ON t.oid = e.enumtypid
GROUP BY t.typname;

-- find missing values

SELECT t.:column
FROM :table_to_check t
WHERE :column NOT IN
(
    SELECT e.enumlabel
    FROM pg_enum e
    INNER JOIN pg_type t ON e.enumtypid = t.oid
    WHERE t.typname = :enumtypname;
);