TypeDecorator of PostgreSQL ENUM won't create type

[Pedro Romano]

Executing the following test code:

import sqlalchemy as sa

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.dialects import postgresql

Base = declarative_base()

class MyEnum(sa.types.TypeDecorator):

    impl = postgresql.ENUM

class Test(Base):

    __tablename__ = 'test'

    id = sa.Column(sa.Integer, primary_key=True)

    enum_column = sa.Column(MyEnum('1', '2', '3', name='my_enum_type'))

engine = sa.create_engine('postgresql://scott:tiger@localhost:5432/sqlalchemy_test')

Base.metadata.create_all(engine)

Results in the exception (traceback omitted):

sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "my_enum_type" does not exist

LINE 4:  enum_column my_enum_type,

                     ^

 '\nCREATE TABLE test (\n\tid SERIAL NOT NULL, \n\tenum_column my_enum_type, \n\tPRIMARY KEY (id)\n)\n\n' {}

Which means the PostgreSQL enumerate type isn't being created as it would have been if the postgresql.ENUM had been used directly in the column definition instead of the decorated MyEnum.

Is this behaviour by design or should the decorated column type be expected to also create the corresponding PostgreSQL enumerate type?

Thanks in advance for any feedback regarding this.

--Pedro.

[pasi...@gmail.com]

Hi Pedro

I do it like this

from sqlalchemy.util import KeyedTuple

visibility = KeyedTuple(['public', 'private', 'custom'], labels=['public', 'private', 'custom'])

class Test(Base):

    __tablename__ = 'test'

    visibility = Column(Enum(name="visibility", *visibility._asdict().values()), nullable=False)

in your python code you can then work with this keyedtuple

if 'hello' == visibility.public:

    print "yes"

else

    print "no"

you see that is registered as enum type

select * from pg_type where typname = 'visibility';

select * from pg_enum where enumlabel in ('public', 'private', 'custom');

it works pretty fine

[Pedro Romano]

Thank you very much for your suggestion. It actually hadn't occurred to me to use SQLAlchemy's KeyedTuple instances to defined enumerations.

However, if some processing of the bind parameter or the result value to/from the database is required, being able to create a TypeDecorator for an Enum column would still be very useful to avoid boilerplate code.

--Pedro.

[Michael Bayer]

On Jan 24, 2014, at 5:24 AM, Pedro Romano <pmc...@gmail.com> wrote:

Which means the PostgreSQL enumerate type isn't being created as it would have been if the postgresql.ENUM had been used directly in the column definition instead of the decorated MyEnum.

Is this behaviour by design or should the decorated column type be expected to also create the corresponding PostgreSQL enumerate type?

Thanks in advance for any feedback regarding this.

please use this approach in the case of a type like ENUM which requires special schema-level steps:

class MyEnum(sa.types.TypeDecorator, sa.types.SchemaType):

    impl = postgresql.ENUM

    def _set_parent(self, column):

        self.impl._set_parent(column)

class Test(Base):

    __tablename__ = 'test'

    id = sa.Column(sa.Integer, primary_key=True)

    enum_column = sa.Column(MyEnum('1', '2', '3', name='my_enum_type'))

engine = sa.create_engine('postgresql://scott:tiger@localhost/test', echo=True)

Base.metadata.create_all(engine)

[Pedro Romano]

Thank you Michael! That did the trick.

--Pedro.