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='')
2
Answers
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.
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.