dkLab | Constructor | PostgreSQL 8.3 ALTER ENUM emulation: element addition/removal is possible 

Site map :: Project «Orphus» :: Constructor

Discuss article in the forum Download source archive On the page: How to install The problem Usage samples: enum_add() and enum_del()

Download ENUM "on the fly" altering functions for PostgreSQL 8.3:
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 (at least in 8.3.3). So, you could say

copy code to clipboard 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.

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.

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

Битопливные котлы с раздельными камерами сгорания Kiturami KRM. | Автобусы паз . Российские автобусы на Грузовик.ру | www.x-point.ru - купить ноутбук