On 02/08/2012 07:29 PM, Jackson, Cameron wrote:
>
> 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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
