Enum in Postgres is only supported since 8.3

[Former user]

I recently ran into ProgrammingError due to missing Enum support when deploying some stuff on a server running PostgreSQL-8.1.

Documentation should mention that Enum on Postgres is supported only with 8.3+ as I don't think it really makes sense to detect version and fall back to the string with checks.

[Mike Bayer]

the Enum type is a generic type that works on all backends, including PG pre 8.3 - here's the doc:

 By default, uses the backend's native ENUM type if available, 
    else uses VARCHAR + a CHECK constraint.

so detecting that ENUM is not possible on a particular version and falling back to a generic version is the correct behavior.

If you want to use only a "real" Postgresql ENUM, you'd be better off using sqlalchemy.dialects.postgresql.ENUM.

We can add a line to Enum "this behavior includes backends that only provide ENUM as of a recent version, such as Postgresql 8.3, for example - see the ENUM type within the dialect itself for the purely native version", if that's the issue here.

[Former user]

Hmm.. I was using the basic Enum, but it didn't fall back to the VARCHAR + CHECK version: PostgreSQL version is 8.1.21

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "brondb/__init__.py", line 39, in init_db
    Base.metadata.create_all(bind=engine)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/schema.py",
line 1975, in create_all
    bind.create(self, checkfirst=checkfirst, tables=tables)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1647, in create
    connection=connection, **kwargs)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1682, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/sql/visitors.py",
line 77, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/ddl.py",
line 42, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/sql/visitors.py",
line 77, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/ddl.py",
line 52, in visit_table
    listener('before-create', table, self.connection)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/util.py",
line 467, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/types.py",
line 1312, in _on_table_create
    t._on_table_create(event, target, bind, **kw)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/dialects/postgresql/base.py",
line 271, in _on_table_create
    self.create(bind=bind, checkfirst=True)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/dialects/postgresql/base.py",
line 264, in create
    bind.execute(CreateEnumType(self))
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1157, in execute
    params)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1210, in _execute_ddl
    return self.__execute_context(context)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1268, in __execute_context
    context.parameters[0](0), context=context)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1367, in _cursor_execute
    context)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/base.py",
line 1360, in _cursor_execute
    context)
  File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/engine/default.py",
line 277, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
near "ENUM" at character 27
 "CREATE TYPE privileges AS ENUM ('user','admin')" {}

[Mike Bayer]

then that is a bug. This should be fixed in 3eab7982537426d4dc1c467df1371dea3b0a0a4e. Can you please confirm it works on your PG 8.1 setup, thanks.

[Former user]

Now works on both 8.1 and 8.4 :)

[Mike Bayer]

Removing milestone: 0.6.2 (automated comment)