PostgreSQL: Using ‘ALTER TYPE’ to change a custom data type definition

Introduction

In PostgreSQL, managing and updating custom data types is essential for maintaining the database’s adaptability. The ‘ALTER TYPE’ command allows for modifications without the need to recreate data types from scratch.

Understanding ‘ALTER TYPE’

The ‘ALTER TYPE’ command in PostgreSQL is used for modifying existing enum and composite types. This powerful feature helps to respond quickly to changes in business requirements without affecting existing data.

-- Syntax for altering an enum type
ALTER TYPE enum_type_name ADD VALUE 'new_enum_value';

-- Syntax for renaming an enum value
ALTER TYPE enum_type_name RENAME VALUE 'old_enum_value' TO 'new_enum_value';

-- Syntax for adding an attribute to a composite type
ALTER TYPE composite_type_name ADD ATTRIBUTE attribute_name data_type;

-- Syntax for renaming attributes
ALTER TYPE composite_type_name RENAME ATTRIBUTE attribute_name TO new_attribute_name;

Adding New Enum Values

When you have an enum type that needs a new value, you can use ‘ALTER TYPE’ to include it effortlessly.

-- Example of adding an enum value
ALTER TYPE user_status ADD VALUE 'suspended';

Renaming Enum Values

Sometimes, enum values require renaming to be more descriptive or accurate.

-- Example of renaming an enum value
ALTER TYPE user_status RENAME VALUE 'temp_inactive' TO 'inactive';

Changing Composite Types

Composite types are a collection of different data types, representing the structure of a row or a record in PostgreSQL.

-- Adding a new attribute to a composite type
ALTER TYPE user_profile ADD ATTRIBUTE phone_number VARCHAR(15);

-- Renaming an attribute of a composite type
ALTER TYPE user_profile RENAME ATTRIBUTE fullname TO full_name;

Handling Dependent Objects

It’s crucial to ensure that altering a type does not negatively affect dependent objects such as tables or functions. PostgreSQL offers various ways to manage these dependencies.

-- Use CASCADE to automatically update dependent objects
ALTER TYPE user_status ADD VALUE 'probation' CASCADE;

-- Use RESTRICT to prevent change if there are dependencies
ALTER TYPE user_status ADD VALUE 'probation' RESTRICT;

Advanced Usage: Modifying Enum Values in Tables

Modifying enum types becomes more complex when the type is already in use by a table. Advanced techniques are required for safe changes.

-- Step by step process for updating an enum value in a table
ALTER TYPE user_status ADD VALUE 'temporary' BEFORE 'active';
UPDATE users SET status = 'temporary' WHERE status = 'temp_inactive';
ALTER TYPE user_status RENAME VALUE 'temp_inactive' TO 'inactive';

Using Functions and Schema Changes

PostgreSQL’s flexibility allows for creating functions that interact with types, and ‘ALTER TYPE’ can dynamically reflect schema changes in these user-defined functions.

-- Create a function using a composite type
CREATE FUNCTION get_user_profile(INTEGER) RETURNS user_profile AS $ 
... 
$ LANGUAGE sql;
-- Add a new attribute and it reflects in the function
ALTER TYPE user_profile ADD ATTRIBUTE birthday DATE;

Conclusion

PostgreSQL’s ‘ALTER TYPE’ command facilitates fluid and dynamic schema evolution. By utilizing this feature, developers can ensure that their applications continue to function seamlessly as business requirements evolve and expand.