Thread: Question about the enum type
I was playing around with the enum type today. I was toying around with
a schema to model information about baseball, and decided to create an
enum named position:
tjhart=# create type position as enum('pitcher', 'catcher', 'first
base', 'second base', 'third base', 'short stop', 'left field', 'center
field', 'right field', 'designated hitter', 'pinch hitter');
CREATE TYPE
At first, I thought I had done something wrong:
tjhart=# select 'pitcher'::position;
ERROR: syntax error at or near "position"
LINE 1: select 'pitcher'::position;
It took a bit of fumbling and reading - and closer inspection of the
following before I determined what happened:
tjhart=# \dT+ public.*;
List of data types
Schema | Name | Internal name | Size | Description
--------+------------+---------------+------+-------------
public | "position" | position | 4 |
(1 row)
tjhart=# select 'right field'::"position";
position
-------------
right field
(1 row)
tjhart=#
The example 'mood' enum in the documentation isn't quoted when it's
created.
I noticed that 'position' is a function, but I can create types with
the same name as other functions (abs), and the name isn't quoted. I
also tried creating an enum type with a reserved word:
tjhart=# create type select as enum('foo');
ERROR: syntax error at or near "select"
LINE 1: create type select as enum('foo');
^
I'm just toying around, so this isn't high priority. I'll probably
change the name of the enum to fielding_position for clarity's sake
anyway. But for my own education - what's so unique about the name
'position'?
Tim Hart
> I'm just toying around, so this isn't high priority. I'll probably > change the name of the enum to fielding_position for clarity's sake > anyway. But for my own education - what's so unique about the name > 'position'? It's a string manipulation function: http://www.postgresql.org/docs/8.3/interactive/functions-string.html -- Postgresql & php tutorials http://www.designmagick.com/
Chris wrote: > >> I'm just toying around, so this isn't high priority. I'll probably >> change the name of the enum to fielding_position for clarity's sake >> anyway. But for my own education - what's so unique about the name >> 'position'? > > It's a string manipulation function: > > http://www.postgresql.org/docs/8.3/interactive/functions-string.html Forgot to say, you can see a list of reserved words in the manual too: http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html and it's reserved there too. -- Postgresql & php tutorials http://www.designmagick.com/
On Feb 17, 2008, at 21:24 , Tim Hart wrote: > But for my own education - what's so unique about the name 'position'? It's an SQL keyword: http://www.postgresql.org/docs/8.3/interactive/sql-keywords- appendix.html Michael Glaesemann grzm seespotcode net
On Feb 17, 2008, at 10:41 PM, Chris wrote: > Chris wrote: >>> I'm just toying around, so this isn't high priority. I'll probably >>> change the name of the enum to fielding_position for clarity's sake >>> anyway. But for my own education - what's so unique about the name >>> 'position'? >> It's a string manipulation function: >> http://www.postgresql.org/docs/8.3/interactive/functions-string.html If the issue were simply that it were a function name, than I would have expected that attempting to create an enum type of 'abs' to also result in a quoted type. That didn't happen, and I stated so in my initial e-mail. > Forgot to say, you can see a list of reserved words in the manual too: > > http://www.postgresql.org/docs/8.3/interactive/sql-keywords- > appendix.html > > and it's reserved there too. Yup. Saw that about 3 minutes after I sent the original question. I sent a follow-up covering that. What I didn't state in that e-mail was that I tried to create an enum type that was another name in the list which was non-reserved (cannot be function or type). In that case, I did see the automatic quoting behavior I saw with position. Thanks for the prompt reply. Tim Hart
Tim Hart <tjhart@mac.com> writes: > On Feb 17, 2008, at 10:41 PM, Chris wrote: > It's a string manipulation function: > http://www.postgresql.org/docs/8.3/interactive/functions-string.html > If the issue were simply that it were a function name, than I would > have expected that attempting to create an enum type of 'abs' to also > result in a quoted type. The reason "position" is special is that the SQL spec calls out weird specialized syntax for it: <string position expression> ::= POSITION <left paren> <string value expression> IN <string value expression> <right paren> There's no way to handle this random use of IN rather than comma, except by having a specialized grammar production, which requires POSITION to be a keyword. In contrast, "abs" isn't a keyword at all in Postgres' eyes --- it's just a function that happens to appear in the system catalogs. regards, tom lane
On Feb 17, 2008, at 11:21 PM, Tom Lane wrote:
> The reason "position" is special is that the SQL spec calls out weird
> specialized syntax for it:
Given the spec, I completely understand.
Given the roundabout way I discovered the nature of the problem, I'm
curious:
At the time I executed the statement
create type position as enum('pitcher', 'catcher', 'first base',
'second base', 'third base', 'short stop', 'left field', 'center
field', 'right field', 'designated hitter', 'pinch hitter');
Would it have been reasonable to expect some kind of notice or warning
message stating that 'position' was special, and
<double-quote>position<double-quote> would be used instead?
Given the non-trivial list of reserved and non-reserved words, I can
imagine that this situation has risen before - and probably will again.
This is not a rant or a gripe, BTW - I'm poking and prodding this
subject in order to be more well informed.
Tim
Tim Hart <tjhart@mac.com> writes:
> At the time I executed the statement
> create type position as enum('pitcher', 'catcher', 'first base',
> 'second base', 'third base', 'short stop', 'left field', 'center
> field', 'right field', 'designated hitter', 'pinch hitter');
> Would it have been reasonable to expect some kind of notice or warning
> message stating that 'position' was special, and
Perhaps, although I'm not sure how to do that without generating
nuisance warnings in some cases. One problem is that the CREATE TYPE
code has no idea whether you typed position or "position", and surely
a warning wouldn't be desirable in the second case.
> <double-quote>position<double-quote> would be used instead?
The way you phrase that makes me think you misunderstand what's
happening here. The name of the type isn't "position" with double
quotes, it's just position. You have to double-quote it when you
use it to prevent the parser from thinking that the special SQL
POSITION function call syntax is coming up. There are other ways
to do that though, for example if you write public.position (or whatever
schema it's in) then you won't need double quotes.
> Given the non-trivial list of reserved and non-reserved words, I can
> imagine that this situation has risen before - and probably will again.
Yeah, the SQL committee is doing no one any favors with their fondness
for bizarre special-case syntax for what could be perfectly ordinary
function calls. In the example at hand, this is actually completely
ambiguous:
position('a' in ('b'))
Is that a single argument that happens to be a degenerate IN-scalar-list
expression, or is it a spec-compliant invocation of 2-argument POSITION?
Experimentation shows that PG thinks it's the latter, but I sure
wouldn't have taken a bet on that in advance.
regards, tom lane
On Feb 18, 2008, at 12:10 AM, Tom Lane wrote: >> Would it have been reasonable to expect some kind of notice or warning >> message stating that 'position' was special, and >> <double-quote>position<double-quote> would be used instead? > > The way you phrase that makes me think you misunderstand what's > happening here. The name of the type isn't "position" with double > quotes, it's just position. You have to double-quote it when you > use it to prevent the parser from thinking that the special SQL > POSITION function call syntax is coming up. There are other ways > to do that though, for example if you write public.position (or > whatever > schema it's in) then you won't need double quotes. I did misunderstand. I understand now that the quotes are used to call out the string literally, and to avoid syntax-related parsing. Thanks for the help. Tim