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

Question

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='')

Please show us your schema in Postgres. Run `psql` and `\d+ user` (or whatever the table is called). — Schwern, Sep 27 '22 at 19:22
What is the value that causes the error? Is there a reason you turned `native_enum` off? — Schwern, Sep 27 '22 at 19:25
@Schwern Not really. It was part of a code snippet that I copied. I'm not really sure what it does. All I know is when I select a role with more than 6 variables the program throws the error. — Mike C., Sep 27 '22 at 19:28
If you don't know what the code you're using is doing, start with [the documentation](https://docs.sqlalchemy.org/en/14/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum). — Schwern, Sep 27 '22 at 19:34

score 0 · Answer 1 · answered Sep 27 '22 at 19:47

0

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'
)

answered Sep 27 '22 at 19:47

Schwern

153,029
25
195
336

I am still getting the same error, but only when I enter one of the strings with over 6 characters. – Mike C. Sep 27 '22 at 19:59
@MikeC. If the table already exists, it will not have your changes. You need to either drop and recreate the table, or do a [migration](https://docs.sqlalchemy.org/en/14/core/metadata.html#altering-database-objects-through-migrations). – Schwern Sep 27 '22 at 22:07
I also read something about the length of the enum column is by default the longest string size in the Enums. I recently added the longer ones. I’ve done migrations but that didn’t work. I haven’t tried just dropping the table. That might just reset everything. There is also a length parameter but I couldn’t get that to work either. – Mike C. Sep 27 '22 at 22:14
Just an update. Deleting the table worked. Thank you, – Mike C. Sep 27 '22 at 22:40

score 0 · Answer 2 · answered Sep 28 '22 at 11:58

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.

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

2 Answers2