How do I cange the string size value for ENUM in Sqlalchemy / PostgreSQL-CodePudding

I am writing a Flask app with SqlAlchemy and PostgreSQL. When I try to enter a value into an enum field that has more than six characters, I get the following error: sqlalchemy.exc.DataError: (psycopg2.errors.StringDataRightTruncation) value too long for type character varying(6)

I am assuming that I need the change the ENUM field's string length. Does anyone know how to do this?

Here is my model. The relevant column is role.

class User(db.Model, UserMixin): #Parent table to profile
    ROLE = OrderedDict([        
        ('admin', 'Admin'),
        ('carrier', 'Carrier'),
        ('driver', 'Driver'),
        ('dispatcher', 'Dispatcher'),
        ('super', 'Super'),
    ])
    id = db.Column(db.Integer, primary_key = True)
    
    user = db.relationship('Profile', backref='User', passive_deletes=True)
    percentage = db.relationship('Percentage', backref='User', passive_deletes=True)

    company_id = db.Column(db.Integer)
    role = db.Column(db.Enum(*ROLE, name='role_types', native_enum=False),
                     index=True, nullable=False, server_default='admin')
    active = db.Column('is_active', db.Boolean(), nullable=False,
                       server_default='1')
    username = db.Column(db.String(24), unique=True, index=True)
    firstname = db.Column(db.String(50), nullable=False)
    lastname = db.Column(db.String(50), index=True,nullable=False)
    email = db.Column(db.String(255), unique=True, index=True, nullable=False,
                      server_default='')
    password = db.Column(db.String(256), nullable=False, server_default='')

CodePudding user response：

native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR check constraint for all backends. When False, the VARCHAR length can be controlled with Enum.length; currently “length” is ignored if native_enum=True.

With native_enum=false this is not a true enum, but just a varchar field. Python should guess at the correct length. However, Enum takes a list, not a dict. The use of an OrderedDict could be interfering.

You can also try leaving native_enum at the default, though enums on Postgres have some cavets.

Try using the PostgreSQL ENUM type.

role = db.Column(
  ENUM('admin', 'carrier', 'driver', 'dispatcher', 'super', name='role_types'),
  index=True, nullable=False, server_default='admin'
)

CodePudding user response：

When the table was initially set up, PostGreSQL set the length to the value of the longest Enum. However, when I added new Enums and did the migration with Flask-Migrate, the length restriction remained in place. So adding the new longer enums did nothing to change the initial length restriction. I even added the length parameter but that didn't work either. The solution as mentioned in the comments by @Schwern was to drop the table, then do the migrations. If there is a way the reset the maximum length of the enums without dropping the table, I have not found it.