Cannot remove Postgres ENUM

Question

I have a enum in postgres. It was defined via sql alchemy like so:

sa.Column('state', postgresql.ENUM(u'ACTIVE', u'STOPPED', u'FAILED', name='ProcessState'), nullable=True),

I cannot remove it, but it also doesnt seem to exist:

database=# select NULL :: ProcessState;
ERROR:  type "processstate" does not exist
LINE 1: select NULL :: ProcessState
                       ^
database=# SELECT n.nspname AS "schema", t.typname
     , string_agg(e.enumlabel, '|' ORDER BY e.enumsortorder) AS enum_labels
FROM   pg_catalog.pg_type t 
JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
JOIN   pg_catalog.pg_enum e ON t.oid = e.enumtypid  
GROUP  BY 1,2;
 schema |  typname  |      enum_labels      
--------+-----------+-----------------------
 public | ProcessState | ACTIVE|STOPPED|FAILED
(1 row)


database=# drop type public.ProcessState;
ERROR:  type "public.processstate" does not exist
database=# drop type ProcessState;
ERROR:  type "processstate" does not exist

I'm at a loss. It seems to exist and not exist at the same time, and I cannot remove it, or re-add it. Re-adding with SQL Alchemy results in an error:

File "python2.7/site-packages/sqlalchemy/engine/default.py", line 462, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "ProcessState" already exists
 [SQL: 'CREATE TYPE "ProcessState" AS ENUM (\'ACTIVE\', \'STOPPED\', \'FAILED\')']

Any ideas what is going on? Any ideas how to fix this? I'd like to be able to reliably add this type and drop it (without having to drop the whole database).

Answer 1

It looks like SQLAlchemy writes this type to the database with quotes (see the SQL in the error message).

Try:

DROP TYPE "ProcessState";