Sling Academy
Home/PostgreSQL/PostgreSQL: Using ‘ALTER TYPE’ to change a custom data type definition

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

Last updated: January 04, 2024

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.

Next Article: Setting FOREIGN KEY constraint in PostgreSQL

Previous Article: PostgreSQL: Using ‘CREATE TYPE’ to create custom data types

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB