Re: Reserved words and delimited identifiers

On Tue, Nov 29, 2011 at 11:21 PM, Joe Abbate <jma(at)freedomcircle(dot)com> wrote:
> Why does it allow quoting of "integer" as the table name and the column
> name, but not as the type name?  Furthermore,

Because there's nothing called "integer" in the pg_type catalog. It's
not really a type name; as Tom says, it's some random key word
invented by the SQL committee that we map to a type name inside the
parser somewhere. On the other hand, the table and column names have
the opposite problem: you CAN'T use random keywords there; you can
ONLY use identifiers. So in one case you must quote because you need
to have an identifier rather than a keyword, whereas in the other case
you must not quote because you need to have a keyword rather than an
identifier.

> 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.

What else would you expect? It would be extremely strange if you
created a type called "integer" and could not then define a column of
type "integer". The key point is that what "integer" means has
nothing to do with what the unquoted identifier INTEGER means; you
could make "integer" be a domain over text if you wanted. You're not
making it allow quoting; you're defining a completely new data type
that happens to have a name very similar to an existing keyword (but
when quoted, it's not a keyword, of course). On a similar note, it's
not contradicting the SQL key words table, because once you quote it,
*it's no longer a keyword*. The whole point of quoting identifiers is
that it allows you to use names that would ordinarily be keywords as
non-keywords.

> 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?

There is no such general practice. In general, if something is an
identifier, you can quote it. If it's a keyword and you want to make
it an identifier, you can quote it to force that interpretation.
Table and column names must be identifiers, so they can always be
quoted; if the identifiers happen to be confusable with reserved
keywords then you *must* quote them to avoid having them interpreted
as keywords rather than identifiers. However, types can be referred
to using either a keyword, or an identifier. Those that are
identifiers can be quoted; those that are keywords cannot.

I will cheerfully admit that this is confusing and inconvenient, and I
didn't understand it either until I implemented pg_dump
--quote-all-identifiers. However, I'm not sure there's any easy way
to improve the situation. For example, what are we to do with
character varying? That can't very well be treated as an identifier,
because it's got a space in the middle. And even if we could hack
around that, it's no good to treat it as "character varying" anyway,
because then you'd have "character varying" != "varchar", and we
certainly don't want those to be different types. What the current
code does is map character varying to varchar under the hood, and then
sometimes map it back on output. This does break things for client
code that just wants to quote everything in the world (and you're not
the first person to run up against this problem; I seem to recall
noticing some sketchy-looking code in pgAgent or pgAdmin the last time
I looked...) but don't see any realistic alternative that's less evil
so I think we're stuck with it...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company