![]() This could make this alternative unfeasible to your application. Once it has that lock, it will perform a full table scan to migrate the data and ensure it conforms to the new type. This lock is the most restrictive of all locks in PostgreSQL: your transaction is the only transaction that can access that table while it exists. However, depending on the size of the table, the ALTER TABLE command can have serious performance implications, because it acquires an ACCESS EXLUSIVE lock. TYPE mood USING current_mood:: text::mood - Remove the old enum. ALTER TABLE person ALTER COLUMN current_mood ![]() Update existing columns to use the new enum. CREATE TYPE mood AS ENUM ( 'sad', 'happy' ) - Perform data migration to remove the enum value that will be dropped. ALTER TYPE mood RENAME TO mood_old - Create the new enum. For the most straightforward cases, you will need something like this: To do that, you need to create a new enum in the form you want it to have, and then change all columns to use that new type (well, technically there are alternatives, see below).Ĭreating a new enum and swapping existing columns to use the new type can be tricky. But enums also come with some limitations: for example, you cannot remove an existing value from an enum. Although enums are intended for static sets of values, you can add values to the type and rename existing values. They come with ordering (from the order in which the values were declared), type safety (you cannot compare two values coming from different enums, even if their string or numerical representations are the same), and space efficiency (they are stored in the tuples as references to the actual enum values that exist in the catalogue tables). CREATE TYPE mood AS ENUM ( 'sad', 'ok', 'happy' ) CREATE TABLE person (
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |