Oddity in column specifications for table creation
It seems that in create table I cannot specify the schema of a built-in
type that has a length specifier. Nor can I double-quote the type name.
Is this correct/expected behaviour, and are there work-arounds?
This works fine:
create table "public"."additional" (
"str1" "pg_catalog"."text" not null,
"str2" "pg_catalog"."varchar"
);
This does not:
create table "public"."additional" (
"str1" "pg_catalog"."text" not null,
"str2" "pg_catalog"."varchar"(40)
);
ERROR: syntax error at or near "("
LINE 3: "str2" "pg_catalog"."varchar"(40)
Or this:
create table "public"."additional" (
"str1" "pg_catalog"."text" not null,
"str2" "pg_catalog".varchar(40)
);
ERROR: syntax error at or near "("
LINE 3: "str2" "pg_catalog".varchar(40)
Or this:
create table "public"."additional" (
"str1" "pg_catalog"."text" not null,
"str2" pg_catalog.varchar(40)
);
ERROR: syntax error at or near "("
LINE 3: "str2" pg_catalog.varchar(40)
But this does:
create table "public"."additional" (
"str1" "pg_catalog"."text" not null,
"str2" varchar(40)
);
For now, I will simply not quote or schema-specify anything from
pg_catalog (though I guess I should force the search path to only
include pg_catalog in this case) but I find the limitation kinda odd.
This is in 8.1 and 8.2
__
Marc
Marc Munro <marc@bloodnok.com> writes:
This works fine:
"str2" varchar(40)
This does not:
"str2" "pg_catalog"."varchar"(40)
Yeah. That's because in all existing PG releases, type modifiers are
handled by hard-wired grammar productions that *only* work for VARCHAR,
CHARACTER VARYING, and so on, treated as keywords.
Teodor did some remarkable work for 8.3, fixing things so that any type
name could have modifiers attached, without (we hope ;-)) breaking any
cases that worked before. It had previously been assumed that this was
impossible, because a type-name-plus-modifier looks just about
indistinguishable from a function call, but he managed to find a way
that side-stepped all the grammatical ambiguities. Your examples all
work fine in CVS HEAD.
regards, tom lane
On Tue, 2007-11-12 at 19:32 -0500, Tom Lane wrote:
Marc Munro <marc@bloodnok.com> writes:
This works fine:
"str2" varchar(40)
This does not:
"str2" "pg_catalog"."varchar"(40)Yeah. That's because in all existing PG releases, type modifiers are
handled by hard-wired grammar productions that *only* work for VARCHAR,
CHARACTER VARYING, and so on, treated as keywords.Teodor did some remarkable work for 8.3, fixing things so that any type
name could have modifiers attached, without (we hope ;-)) breaking any
cases that worked before. It had previously been assumed that this was
impossible, because a type-name-plus-modifier looks just about
indistinguishable from a function call, but he managed to find a way
that side-stepped all the grammatical ambiguities. Your examples all
work fine in CVS HEAD.
Cool.
Thanks, Tom for the response, and Teodor for fixing my problem before I
even knew I had it.
__
Marc