Thread: BUG #12819: CREATE TYPE fails within CREATE SCHEMA

The following bug has been logged on the website:

Bug reference:      12819
Logged by:          Chris Craig
Email address:      postgresql.org@ciotog.net
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 14.04.1 LTS
Description:

When creating objects within a CREATE SCHEMA definition, "CREATE TYPE" fails
with 'ERROR:  syntax error at or near "TYPE"'.

Eg:
postgres=# CREATE SCHEMA foo
postgres-# CREATE TYPE foo.bar AS ENUM('ONE','TWO','THREE');
ERROR:  syntax error at or near "TYPE"
LINE 2: CREATE TYPE foo.bar AS ENUM('ONE','TWO','THREE');
               ^

postgresql.org@ciotog.net writes:
> When creating objects within a CREATE SCHEMA definition, "CREATE TYPE" fails
> with 'ERROR:  syntax error at or near "TYPE"'.

That's not supported; per the manual, we only allow these things within a
run-on CREATE SCHEMA:

    Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE
    SEQUENCE, CREATE TRIGGER and GRANT are accepted as clauses within
    CREATE SCHEMA.

That's basically driven by what was required in SQL92.  It's unlikely
that we're going to worry about extending that set much, because the
lack of separating semicolons means that we risk syntax ambiguities
anytime we add more options.  We could only resolve such problems by
making more keywords fully reserved, which is a disadvantage that greatly
outweighs any value anyone might see in this syntax for CREATE SCHEMA.

My advice: use a simple CREATE SCHEMA and separate commands for the
contained objects.  You can wrap the whole thing in a transaction
if you're concerned about making it atomic.

            regards, tom lane

On 3/2/2015 8:55 AM, postgresql.org@ciotog.net wrote:
> When creating objects within a CREATE SCHEMA definition, "CREATE TYPE" fails
> with 'ERROR:  syntax error at or near "TYPE"'.
>
> Eg:
> postgres=# CREATE SCHEMA foo
> postgres-# CREATE TYPE foo.bar AS ENUM('ONE','TWO','THREE');
> ERROR:  syntax error at or near "TYPE"
> LINE 2: CREATE TYPE foo.bar AS ENUM('ONE','TWO','THREE');
>                 ^
>


no ; on the first command, so that got read as CREATE SCHEMA foo CREATE
TYPE ....    which isn't a valid command.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 Tom>     Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE
 Tom>     SEQUENCE, CREATE TRIGGER and GRANT are accepted as clauses within
 Tom>     CREATE SCHEMA.

 Tom> That's basically driven by what was required in SQL92.  It's
 Tom> unlikely that we're going to worry about extending that set much,
 Tom> because the lack of separating semicolons means that we risk
 Tom> syntax ambiguities anytime we add more options.

CREATE and GRANT are both fully reserved already; what potential
ambiguities are introduced by adding more CREATE statement variants to
that list? (all the spec's clauses for the body of CREATE SCHEMA begin
with either CREATE or GRANT)

--
Andrew (irc:RhodiumToad)