Skip to content

Enum in Postgres is only supported since 8.3 #1836

New issue
New issue
Closed
Closed
Enum in Postgres is only supported since 8.3#1836
Labels
bugSomething isn't workingpostgresql
Milestone
 
0.6.2
@sqlalchemy-bot

Description

@sqlalchemy-bot
sqlalchemy-bot
opened on Jun 25, 2010
Collaborator

Migrated issue, originally created by Anonymous

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.

Activity

sqlalchemy-bot

sqlalchemy-bot commented on Jun 25, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 25, 2010
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

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.

sqlalchemy-bot

sqlalchemy-bot commented on Jun 25, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 25, 2010
CollaboratorAuthor

Anonymous wrote:

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')" {}
sqlalchemy-bot

sqlalchemy-bot commented on Jun 25, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 25, 2010
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

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

sqlalchemy-bot

sqlalchemy-bot commented on Jun 25, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 25, 2010
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • set milestone to "0.6.2"
sqlalchemy-bot

sqlalchemy-bot commented on Jun 26, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 26, 2010
CollaboratorAuthor

Anonymous wrote:

Now works on both 8.1 and 8.4 :)

sqlalchemy-bot

sqlalchemy-bot commented on Jun 26, 2010

@sqlalchemy-bot
sqlalchemy-bot
on Jun 26, 2010
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
sqlalchemy-bot
closed this as completedon Jun 26, 2010
sqlalchemy-bot
added
postgresql
bugSomething isn't working
on Nov 27, 2018
sqlalchemy-bot
added this to the 0.6.2 milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingpostgresql

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions
          Enum in Postgres is only supported since 8.3 · Issue #1836 · sqlalchemy/sqlalchemy