ARRAY of ENUM doesn't do any validation

[Rachid Belaid]

SQLAlchemy is not complaining when I have a model with

 array_enum = Column(ARRAY(Enum(*LIST_OF_ENUM, native_enum=False)))

but doens't seem to do any validation and I have been able to insert value outside the enum in the array.

[Mike Bayer]

we use a CHECK constraint for ENUM. I have no idea what you'd want here, except in-Python validation? I'd call this a feature then? add a flag "python_validation=True" to ENUM, how's that? Since I can't imagine how a CHECK could do this?

[Rachid Belaid]

I was expecting the insert to fail, it seems like no CHECK constraint was added on the array content. To be honest, I don't know if this allowed.

I initially tried native ENUM but the schema creation fail:

 sqlalchemy.exc.CompileError: Postgresql ENUM type requires a name.

I just tried "python_validation=True" but this raise and error:

TypeError: __init__() got an unexpected keyword argument 'python_validation'

I also cannot see it in the doc : http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.Enum Is it in something recent.

I will try to add a @validate to check the item inserted in the ARRAY , not a big deal that ENUM didn't work but as it didn't complain then I though it will be working.

[Mike Bayer]

I just tried "python_validation=True" but this raise and error:

I am suggesting this as a potential new feature.

Right now, ENUM creates a CHAR() plus a CHECK constraint for the values in that CHAR. I don't know how this could be acheived inside of a Postgresql ARRAY.

[Rachid Belaid]

Sorry I misunderstood. It could be useful to have Enum with python validation as it often a pain to migrate. Thanks for your answers.

[Mike Bayer]

the work here is:

1. add flag python_validation=True to sqltypes.Enum

2. add a bind processor that takes effect when this flag is enabled

3. document the flag in the docstring

4. add tests to test/sql/test_types.py

5. changelog message in changelog_XX , depending on which version we're at

[Mike Bayer]

see https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/72/support-native-enum/diff where we might just add validation in all cases for 1.1

[Mike Bayer]

• add changelog and migration notes for new Enum features, fixes #3095, #3292
• reorganize enum constructor to again work with the MySQL ENUM type
• add a new create_constraint flag to Enum to complement that of Boolean
• reinstate the CHECK constraint tests for enum, these already fail /skip against the MySQL backend
• simplify lookup rules in Enum, have them apply to all varieties of Enum equally

→ <<cset df55695f8e99>>

[Mike Bayer]

regression: a SQL expression that concatenates to the ENUM maintains the type ENUM which then fails because the value isn't in the list. totally not-tested use case.