Beginner LevelPostgreSQL
How do custom ENUM data types work in PostgreSQL?
❓ Interview Prompt: Explain ENUM data types (`CREATE TYPE ... AS ENUM`), static validation, storage efficiency (4 bytes), and ALTER TYPE adding values.
PostgreSQL allows creating custom ENUM (Enumerated) data types containing a static list of string values:
Benefits:
•
Storage Efficiency: Internally stored as 4-byte integers on disk while displaying string labels.
•
Type Safety: Rejects invalid string writes at the database layer.
Code Implementation Example
sql
CREATE TYPE order_status AS ENUM ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED');
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
status order_status NOT NULL DEFAULT 'PENDING'
);
-- Append new value to ENUM type
ALTER TYPE order_status ADD VALUE 'REFUNDED' AFTER 'CANCELLED';💡Creating and mutating custom ENUM data types in PostgreSQL.
🎯 Interview Key Takeaway
Use ENUM types to enforce static domain values with low 4-byte storage footprints.
#ENUM#CREATE TYPE#Type Safety#Beginner
Back to all PostgreSQL Questions→