Thread: syntax error with alter type
I’m running Pg 9.6.5 if it matters…
I’m trying to drop a value from an ENUM (type) and it seems like I’m following the fine manual yet I still get an error. For example:
nms=# create type alphabet as enum ('a', 'b', 'c', 'd');
CREATE TYPE
nms=# alter type alphabet drop attribute if exists 'c';
ERROR: 42601: syntax error at or near "'c'"
LINE 1: alter type alphabet drop attribute if exists 'c';
^
LOCATION: scanner_yyerror, scan.l:1086
What am I doing wrong? The goal is to get rid of the ‘c’ value from the enum. Yes, you can assume I’ve already removed of all the ‘c’ values in the table where it’s used.
Or does that statement not do what I think it does and I have to do the “create new type, change the table to use the new type, drop old type, rename new type to old type” routine?
Thanks,
Kevin
On 12/7/18 3:17 PM, Kevin Brannen wrote:
> I’m running Pg 9.6.5 if it matters…
>
> I’m trying to drop a value from an ENUM (type) and it seems like I’m
> following the fine manual yet I still get an error. For example:
>
> nms=# create type alphabet as enum ('a', 'b', 'c', 'd');
>
> CREATE TYPE
>
> nms=# alter type alphabet drop attribute if exists 'c';
>
> ERROR: 42601: syntax error at or near "'c'"
>
> LINE 1: alter type alphabet drop attribute if exists 'c';
>
> ^
>
> LOCATION: scanner_yyerror, scan.l:1086
>
> What am I doing wrong? The goal is to get rid of the ‘c’ value from the
> enum. Yes, you can assume I’ve already removed of all the ‘c’ values in
> the table where it’s used.
https://www.postgresql.org/docs/10/datatype-enum.html
"Although enum types are primarily intended for static sets of values,
there is support for adding new values to an existing enum type, and for
renaming values (see ALTER TYPE). Existing values cannot be removed from
an enum type, nor can the sort ordering of such values be changed, short
of dropping and re-creating the enum type.
>
> Or does that statement not do what I think it does and I have to do the
> “create new type, change the table to use the new type, drop old type,
> rename new type to old type” routine?
>
> Thanks,
>
> Kevin
>
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you
> are not the intended recipient, or a person responsible for delivering
> it to the intended recipient, you are hereby notified that any
> disclosure, distribution, review, copy or use of any of the information
> contained in or attached to this message is STRICTLY PROHIBITED. If you
> have received this transmission in error, please immediately notify us
> by reply e-mail, and destroy the original transmission and its
> attachments without reading them or saving them to disk. Thank you.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 12/7/18 3:17 PM, Kevin Brannen wrote:
> I'm running Pg 9.6.5 if it matters...
>
> I'm trying to drop a value from an ENUM (type) and it seems like I'm
> following the fine manual yet I still get an error. For example:
>
> nms=# create type alphabet as enum ('a', 'b', 'c', 'd');
>
> CREATE TYPE
>
> nms=# alter type alphabet drop attribute if exists 'c';
>
> ERROR: 42601: syntax error at or near "'c'"
>
> LINE 1: alter type alphabet drop attribute if exists 'c';
>
> ^
>
> LOCATION: scanner_yyerror, scan.l:1086
>
> What am I doing wrong? The goal is to get rid of the 'c' value from
> the enum. Yes, you can assume I've already removed of all the 'c'
> values in the table where it's used.
https://www.postgresql.org/docs/10/datatype-enum.html
"Although enum types are primarily intended for static sets of values, there is support for adding new values to an
existingenum type, and for renaming values (see ALTER TYPE). Existing values cannot be removed from an enum type, nor
canthe sort ordering of such values be changed, short of dropping and re-creating the enum type.
>
> Or does that statement not do what I think it does and I have to do
> the "create new type, change the table to use the new type, drop old
> type, rename new type to old type" routine?
Adrian,
Thanks, I hadn't seen that "existing values cannot be removed from an enum type" since I was looking on the ALTER TYPE
page.
So, can you (or anyone) help me understand what "alter type <type> drop attribute" is meant to do? I don't see
"attribute"on the page you reference.
Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential
information.If you are not the intended recipient, or a person responsible for delivering it to the intended recipient,
youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or
attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately
notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them
todisk. Thank you.
Kevin Brannen <KBrannen@efji.com> writes:
> So, can you (or anyone) help me understand what "alter type <type> drop attribute" is meant to do? I don't see
"attribute"on the page you reference.
IIRC, that drops a column from a composite type; it's more or less a
variant spelling of ALTER TABLE DROP COLUMN.
regards, tom lane
Tom Lane wrote:
> Kevin Brannen <KBrannen@efji.com> writes:
> > So, can you (or anyone) help me understand what "alter type <type> drop attribute" is meant to do? I don't see
"attribute"on the page you reference.
> IIRC, that drops a column from a composite type; it's more or less a variant spelling of ALTER TABLE DROP COLUMN.
I'm going to guess this is part of the "SQL Standard", but I'd like to comment that this seems really ... weird. All
languageshave their warts or things they could have done better in hind sight, but this strikes me as very weird.
That being said, I can sort of see why you say that. Looking at the examples, I can see:
ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
which looks a lot like adding a column to a table. It would have been nice to know what "compfoo" is as that would have
clearedup some confusion. I guess I'll make a doc change request.
Of course, the real answer is that someone here in the past should have made a small table of reference values instead
ofcreating an ENUM, then I wouldn't be facing this. Again, hind sight...sigh.
Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential
information.If you are not the intended recipient, or a person responsible for delivering it to the intended recipient,
youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or
attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately
notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them
todisk. Thank you.