Russian version
Add to Del.icio.us
English version
Digg It!

 Old-School dkLab | Constructor | PostgreSQL 8.3+, 9.1+ ALTER ENUM emulation: element addition/removal, transactions 

Site map :: Project Orphus :: Constructor


2008-08-13

Download ENUM "on the fly" altering functions:
dklab_postgresql_enum_2009-02-26.sql

How to install

It's simple: just download, unpack and execute as usual SQL script. The new schema "enum" will be created in you database; it contains all needed functions.

The problem

It is well-known that ALTER TYPE construction for handy PostgreSQL 8.3 ENUM datatype does not exist (in 9.1 ALTER for ENUM exists, but it does not work inside transaction blocks). So, you could say

Listing 1
CREATE TYPE my_enum AS ENUM('first', 'second');

but later - cannot add a new element to the ENUM if that ENUM is referred from any table. You also cannot remove an element from my_enum, even if there are no rows referring to that element.

In PostgreSQL 9.1 there is an "ALTER TYPE my_enum ADD VALUE 'new_value'" operator, but unfortunately it does not work within transaction blocks, so it is almost useless in semi-automatic migration systems. There is no deletion operator for ENUM elements still.

Usage samples: enum_add() and enum_del()

Proposed solution consists of two functions which allow to add or remove ENUM elements with all needed constraints checks. Here are usage samples.

-- Create a ENUM type and a referring table.
CREATE TYPE my_enum AS ENUM('first', 'second');
CREATE TABLE my_table(id INTEGER, my my_enum);

-- Insert test data to the table.
INSERT INTO my_table(id, my) VALUES(1, 'second');

-- Add a new element to the ENUM "on the fly".
SELECT enum.enum_add('my_enum', 'third');

-- Remove an element from the ENUM "on the fly".
SELECT enum.enum_del('my_enum', 'first');
You see, we could remove elements from my_enum if there are no references to them in all database tables. Now let's consider what happen if we would try to remove the element 'second' which is referred from a row of my_table:
-- Remove an element from the ENUM "on the fly".
SELECT enum.enum_del('my_enum', 'first');
! ERROR: Cannot delete the ENUM element my_enum.second: column public.my_table.my contains references

So, the database integrity is not broken while a ENUM element is deleted.

For dummies 

To achieve the maximum performance of enum_del() for ENUM you should create indices on all columns in all tables referring to that ENUM.





Dmitry Koterov, Dk lab. ©1999-2017
GZip
Add to Del.icio.us   Digg It!   Reddit