How do I change enum values after previously creating a table?
Jackson, Cameron
One of my tables used to have something like:
role = Column(Enum('user', 'admin', name = User_Roles))
Now I want to change it to:
role = Column(Enum('user', 'superuser', name = User_Roles))
But I can't figure out how to change the enum values in the database. I've tried dropping the table from pgAdmin and recreating it with SQLAlchemy, which all seems to go fine, but then when I go back to pgAdmin and try to enter a new user with the role 'superuser', I get an error back:
An error has occurred:
ERROR: invalid input value for enum "User_Roles": "superuser"
LINE 1: …, password, role) VALUES ('john'::text, 'pass'::text, 'superuser…
^
I've tried searching through pg_admin for where this enum is being defined, but I can't find it. Can anyone tell me where in pgAdmin I need to go to change or delete the enum, or how to make SQLAlchemy do so when it creates the table?
Cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron...@thalesgroup.com.au | www.thalesgroup.com.au
Conor
One of my tables used to have something like:
role = Column(Enum('user', 'admin', name = User_Roles))
Now I want to change it to:
role = Column(Enum('user', 'superuser', name = User_Roles))
But I can't figure out how to change the enum values in the database. I've tried dropping the table from pgAdmin and recreating it with SQLAlchemy, which all seems to go fine, but then when I go back to pgAdmin and try to enter a new user with the role 'superuser', I get an error back:
An error has occurred:
ERROR: invalid input value for enum "User_Roles": "superuser"
LINE 1: …, password, role) VALUES ('john'::text, 'pass'::text, 'superuser…
^
I'm guessing the old User_Roles type still exists in the
database. You can drop it with "DROP TYPE User_Roles".
I've tried searching through pg_admin for where this enum is being defined, but I can't find it. Can anyone tell me where in pgAdmin I need to go to change or delete the enum, or how to make SQLAlchemy do so when it creates the table?
pgAdmin does not show types by default. You can enable it by
going to File > Options, Browser tab, and checking the "Types"
checkbox.
Since PostgreSQL types are not owned by tables, I don't think
there is a good way to make this automatic. You can try adding an
"after-drop" DDL event that drops the User_Roles type when you
drop the table, but that has its own problems.
If you are looking to migrate table data to use the new enum, a
script like this should work:
ALTER TYPE User_Roles RENAME TO User_Roles_Old;
CREATE TYPE User_Roles AS ENUM ('user', 'superuser');
ALTER TABLE some_table ALTER COLUMN some_column TYPE User_Roles USING
CASE some_column
WHEN 'user'::User_Roles_Old THEN 'user'::User_Roles
WHEN 'admin'::User_Roles_Old THEN 'superuser'::User_Roles
END;
DROP TYPE User_Roles_Old;
-Conor
Jackson, Cameron
(Mike: my problem is solved, but please read my final sentence for an enhancement request)
>>> I'm guessing the old User_Roles type still exists in the database. You can drop it with "DROP TYPE User_Roles".
Yes, this is what I figured was happening, I wasn't sure how to get rid of it though
>>> pgAdmin does not show types by default. You can enable it by going to File > Options, Browser tab, and checking the "Types" checkbox.
Ahhh, *this* is what I needed to know
>>> Since PostgreSQL types are not owned by tables, I don't think there is a good way to make this automatic. You can try adding an "after-drop" DDL event that drops the User_Roles type when you drop the table, but that has its own problems.
Yeah, for reuse reasons, I can see that it makes sense that tables don't own types, and that dropping the table doesn't drop the type.
Mike, may I suggest this as an enhancement: If SQLA is creating a table with an Enum column, and if there's already an Enum with the specified name, it should spit out an error if the list of values for the existing and requested Enums don't match?
Michael Bayer
Mike, may I suggest this as an enhancement: If SQLA is creating a table with an Enum column, and if there's already an Enum with the specified name, it should spit out an error if the list of values for the existing and requested Enums don't match?
------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. -------------------------------------------------------------------------
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Jackson, Cameron
Mike, may I suggest this as an enhancement: If SQLA is creating a table with an Enum column, and if there's already an Enum with the specified name, it should spit out an error if the list of values for the existing and requested Enums don't match?
Maybe, though would that same logic extend to other constructs ? Such as, if you had a Table, and you said metadata.create_all(), that particular Table already exists. Should SQLAlchemy go out, reflect that table and compare all the column names to the Table you have and report an error ? Seems like the same thing.
Alembic does this comparison, but it's more of a helper to write migrations rather than a consistency checker, since we can't reliably match everything about the database to what's defined in Python.
Yeah you're right, it's essentially the same thing. Personally, I was a bit surprised when I discovered that SQLAlchemy just ignores already-created tables, even the model is different to the preexisting table. However, it also means that if you have a script that imports and creates all models, you can use it no matter whether you have changed/added a single model or all of them, so there's that.
I guess I just got a bit tripped up because my SQL/database knowledge perhaps isn't what it should be, so I didn't really understand how an enum would be implemented and that simply trying to redefine it would cause problems. SQLA makes things so easy, it can be easy to get complacent and assume that everything will just work, without thinking about the database!