0

I am trying to update TYPE with query:

ALTER TYPE public.enum_subscription_sub_frequency RENAME ATTRIBUTE "BI-WEEKLY" TO "BI_WEEKLY";

But it give me error ERROR: relation "public.enum_subscription_sub_frequency" does not exist but type is exist.

freq

Kind Help me to complete this.

Screen:

c

ss

Got Answer:

ALTER TYPE public.enum_subscription_sub_frequency RENAME VALUE 'BI-MONTHLY' TO 'BI_MONTHLY';

but i also need to alter multiple value of a TYPE?

ankit
  • 2,591
  • 2
  • 29
  • 54
  • three things to check: 1) is the type in the `public` schema? 2) is your sql client connected to the right db? 3) does your user have the proper permissions? (unlikely to be the error, but worth taking a look) – Jim Jones Aug 27 '18 at 13:12
  • 1
    @JimJones Kindly check Edit question. – ankit Aug 27 '18 at 13:17
  • There is no need to add the solution to the question. The fact that an answer is accepted marks the question as solved. –  Aug 27 '18 at 13:34

1 Answers1

3

Renaming a value of an enum is only supported starting with Postgres 10.

As documented in the manual you have to use rename VALUE, not rename attribute to rename the value of an enum.

Enums values are also string constants, not identifiers. Therefore you need to enclose them in single quotes, not double quotes:

ALTER TYPE public.enum_subscription_sub_frequency RENAME VALUE 'BI-WEEKLY' TO 'BI_WEEKLY';

If you wonder why you get a "type ... does not exist" error with the wrong syntax:

When you use the option RENAME ATTRIBUTE this indicates that a "regular" object type should be changed, so Postgres looks for an "real" object type.

But an "enum type" is not an "object type" and therefor Postgres complains about "type xyz does not exist", rather than a syntax error.

  • Thanks it work. `ALTER TYPE public.enum_subscription_sub_frequency RENAME VALUE 'BI-WEEKLY' TO 'BI_WEEKLY';` – ankit Aug 27 '18 at 13:19
  • I also want to alter more than one value. Please suggest – ankit Aug 27 '18 at 13:19
  • [as documented in the manual](https://www.postgresql.org/docs/current/static/sql-altertype.html) you can only rename one value at a time. –  Aug 27 '18 at 13:21
  • I am able to find there for multiple. Please edit in your query.Its Urgent – ankit Aug 27 '18 at 13:21
  • Please do not extend the scope of your question once you have an answer. [Ask a new question](https://stackoverflow.com/questions/ask) instead (but you already have an answer: you can not rename more than one enum value with a single ALTER TYPE statement) –  Aug 27 '18 at 13:24
  • Thanks for your answer a_horse_no_name. – ankit Aug 27 '18 at 13:28
  • this query solve my problem but when i run this in H2 database it gives me error. PLease suggest me for that. – ankit Aug 31 '18 at 14:44
  • is there any query that run in both h2 and postgresql database? – ankit Aug 31 '18 at 14:46
  • It give syntax error at `VALUE` in `postgres 9.6` but work fine in `Postgres 10.3` – ankit Sep 14 '18 at 06:53