CODEWITHSUNDEEP
postgresql
Maltronic
Maltronic

Reputation: 1802

Adding enumerated types in Postgres

I'm trying to add a new value to an enumerated type in my Postgres database but the type is already in use by various fields. Postgres won't let me do this as the type is already in use.

Previously I've accomplished this by:

  1. copying all fields using the type to a temporary varchar field,
  2. dropping all the fields and functions using the type,
  3. deleting the type,
  4. creating a new type with the extra enumerated value,
  5. Setting all the temp fields and functions back to use the enumerated type.

A big job in any situation, but an impossibly big task if the type is used in dozens of times throughout the DB in tables, views and functions. Surely there must be an easier way just to merely add a new value to an enumerated type?

Many thanks for any help.

Upvotes: 0

Views: 167

Answers (2)

anon
anon

Reputation:

@a_horse_with_no_name covered the optimal way to solve this problem. If a complete re-architecture doesn't suit your fancy, you can take advantage of PostgreSQL's support for transactions in both DDL and DML operations.

So you could, in theory, perform all five of your steps in a single transactional operation. Because of MVCC you will be able to safely make this change and have a minimal functional impact to users of your database. You'll probably incur a huge disk overhead (depending on the size of the tables) and substantial database bloat (if the transaction takes a lot time, the vacuum process won't run).

All of that being said, it's perfectly doable.

Upvotes: 1

Szymon Lipiński
Szymon Lipiński

Reputation: 28634

There will be an easy way in the new 9.1 version: http://developer.postgresql.org/pgdocs/postgres/sql-altertype.html

Upvotes: 0

Related Questions