Add multiple PostgreSQL enum values with one command

Question

Since PostgreSQL 9.1 enum values can be added using

ALTER TYPE my_type ADD VALUE new_value;

However, trying to run this as part of a bigger script gives an error:

ALTER TYPE ... ADD cannot be executed from a function or multi-command string

This makes it a real hassle to script changes to be applied in production, because support staff have to remember that, while most scripts can be run "normally", there are a few "special" scripts that need to be opened in pgAdmin and run manually, piece by piece. I've Googled this and I understand the limitation - enum values cannot be added inside a transaction or part of a "multi-command string". That's OK. I don't need to do that. I just want to add multiple enum values and execute other, unrelated, SQL statements without having to feed Postgres one statement at a time.

In other words: I want to be able to run a single script, both from pgAdmin and from psql, that simply does the same thing that pgAdmin does when I highlight one line at a time and press F5 (run). Is there a way to do this, maybe with plpgsql?

Why don't you use a check constraint instead of an enum. Much more flexible (and portable). alter type add value cannot run in a transaction, so all you need to do is to turn off auto-commit before those statements (I don't know how to do that in pgAdmin, I don't use it) — user330315
– user330315, Commented Dec 24, 2014 at 12:04
You're right, Craig Ringer, "psql -f" already works. a_horse_with_no_name, yes, maybe disabling autocommit would do the trick, but I can't find a way to do that (without starting a transaction myself). SET AUTOCOMMIT = OFF gives an error: "SET AUTOCOMMIT TO OFF is no longer supported" How does "psql -f" do it then? Does it parse the script itself and send it to the server one statement at a time? — EM0
– EM0, Commented Dec 24, 2014 at 16:42
did you found the solution for this? I have the issue with it too when want to run a big script — Kostanos
– Kostanos, Commented May 22, 2019 at 14:57
@Kostanos No, but PostgreSQL 12 should allow this (not tested, just read the docs). — EM0
– EM0, Commented May 23, 2019 at 9:57

EM0 · Accepted Answer · 2019-06-03 13:32:54Z

7

Looks like this will finally be fixed in PostgreSQL 12.

https://www.postgresql.org/docs/12/sql-altertype.html says

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

edited Jun 3, 2019 at 13:32

answered May 23, 2019 at 9:56

EM0

6,6058 gold badges62 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add multiple PostgreSQL enum values with one command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related