Re: Reserved words and delimited identifiers

On 11/29/2011 10:09 PM, Robert Haas wrote:
> On Tue, Nov 29, 2011 at 7:29 PM, Joe Abbate <jma(at)freedomcircle(dot)com> wrote:
>> It seems to me that since a TYPE in a column definition or function
>> argument can be a non-native TYPE, it could be a reserved word and
>> therefore it should always be allowable to quote the TYPE. Can someone
>> please explain why that is not the case?
>
> Type names as they appear in pg_type.typname can always be quoted.
> But some types, like int4, have alternate names - e.g. int4 can be
> specified as integer or int, and foat8 can be specified using the
> two-word phrase double precision. These alternate names are keywords
> when unquoted, but identifiers (with a different meaning) when quoted.

What I don't understand is the following:

pyrseas_testdb=# create table "integer" (c1 integer); drop table "integer";
CREATE TABLE
DROP TABLE
pyrseas_testdb=# create table "integer" ("integer" integer); drop table
"integer";
CREATE TABLE
DROP TABLE
pyrseas_testdb=# create table "integer" ("integer" "integer");
ERROR: type "integer" does not exist
LINE 1: create table "integer" ("integer" "integer");
^
pyrseas_testdb=# create table "integer" ("integer" "INTEGER");
ERROR: type "INTEGER" does not exist
LINE 1: create table "integer" ("integer" "INTEGER");
^

Why does it allow quoting of "integer" as the table name and the column
name, but not as the type name? Furthermore,

pyrseas_testdb=# create domain "integer" as "int4";
CREATE DOMAIN
pyrseas_testdb=# create table t1 ("integer" "integer");
CREATE TABLE
pyrseas_testdb=# create domain "INTEGER" as int4;
CREATE DOMAIN
pyrseas_testdb=# create table t2 ("integer" "INTEGER");
CREATE TABLE

Now that I created a DOMAIN/TYPE named "integer" or "INTEGER"
(contradicting the SQL Key Words table), it does allow quoting. This
behavior appears inconsistent with the general practice that allows
quoting of type names. In other words, why am I allowed to quote
"int4", but not "integer" or "INTEGER" (as a type name?

Joe