You have an ENUM type status with values ('pending', 'approved', 'rejected'). You want to change the order so 'approved' comes first without dropping the type. How can you do this?
hard📝 Application Q9 of 15
PostgreSQL - Advanced Features
You have an ENUM type status with values ('pending', 'approved', 'rejected'). You want to change the order so 'approved' comes first without dropping the type. How can you do this?
AAdd 'approved' before 'pending' using ALTER TYPE, then drop the old 'approved' value
BUse ALTER TYPE ... RENAME VALUE to reorder values
CDrop and recreate the ENUM type with the new order
DUse ALTER TYPE ... ADD VALUE 'approved' BEFORE 'pending' and ignore duplicates
Step-by-Step Solution
Solution:
Step 1: Understand ENUM value order immutability
PostgreSQL does not support reordering existing ENUM values directly.
Step 2: Identify workaround
To change order, drop and recreate the ENUM type with desired order, migrating data if needed.
Final Answer:
Drop and recreate the ENUM type with the new order -> Option C
Quick Check:
ENUM value order change requires type recreation [OK]
Quick Trick:ENUM order can't be changed; recreate type to reorder [OK]
Common Mistakes:
Trying to rename or reorder values directly
Adding duplicate ENUM values
Ignoring data migration when dropping type
Master "Advanced Features" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
