On Tue, Nov 29, 2011 at 11:21 PM, Joe Abbate <j...@freedomcircle.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers