This page:
http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html
States that 'POSITION' is non-reserved, but cannot be a function or type. Slightly confusing as stated - I appreciate that postgres tried to accommodate me. Very mildly annoyed that there wasn't some kind of notice regarding how it helpfully 're-named' my type.
I'm not an experienced administrator though - if this is well-understood behavior, than consider me educated. ;)
Tim
Begin forwarded message:
From: Tim Hart <tjhart@xxxxxxx>
Date: February 17, 2008 8:24:44 PM CST
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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
