Horton

Re: Alter or rename enum value

Jim Nasby <jim.nasby@bluetreble.com>

View thread View on postgr.es
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: <emre@hasegeli.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>, Christophe Pettus <xof@thebuild.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Matthias Kurz <m.kurz@irregular.at>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2016-03-29T06:57:17Z
Lists: pgsql-hackers 
On 3/28/16 4:42 AM, Emre Hasegeli wrote:
> Now, we are using a
> function to replace an enum type on all tables with another one, but
> we are not at all happy with this solution.  It requires all objects
> which were using the enum to be dropped and recreated, and it rewrites
> the tables, so it greatly increases the migration time and effort.

FWIW, there are ways to avoid some of that pain by having a trigger 
maintain the new column on INSERT/UPDATE and then slowly touching all 
the old rows where the new column is NULL.

Obviously would be much better if we could just do this with ENUMs...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

Commits

  1. Relax transactional restrictions on ALTER TYPE ... ADD VALUE (redux).

  2. Partially restore comments discussing enum renumbering hazards.

  3. Allow adding values to an enum type created in the current transaction.