Thread: BUG #2100: CREATE TABLE AS - may not supply table specification

The following bug has been logged online:

Bug reference:      2100
Logged by:          Robert Bengtsson
Email address:      robert@fbt.se
PostgreSQL version: PostgreSQL Data
Operating system:   Windows Server 2003, Web Edition
Description:        CREATE TABLE AS - may not supply table specification
Details:

CREATE TABLE AS seems to be broken.

The following syntax works:

CREATE TABLE l_modeltext WITHOUT OIDS AS (SELECT distinct modeltext as text,
make, model, make_id, model_id from l_modelcode);

while the following generates an error:

CREATE TABLE l_modeltext
(
  id serial NOT NULL,
  text varchar(60),
  make varchar(30),
  model varchar(30),
  make_id int4,
  model_id int4
) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id,
model_id from l_modelcode);

with the following errorcode:

ERROR:  syntax error at or near "AS" at character 94

However, the errorcode is, strangely enough, somewhat dependent on where the
edit-cursor is placed inside pgAdmin.

"Robert Bengtsson" <robert@fbt.se> writes:
> while the following generates an error:

> CREATE TABLE l_modeltext
> (
>   id serial NOT NULL,
>   text varchar(60),
>   make varchar(30),
>   model varchar(30),
>   make_id int4,
>   model_id int4
> ) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id,
> model_id from l_modelcode);

That is not the syntax for CREATE TABLE AS, and I don't see anything in
the reference page for CREATE TABLE AS that would suggest that it is.

I'd suggest creating the table with the columns you want and then doing
INSERT ... SELECT to fill it.

            regards, tom lane

Robert Bengtsson wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2100
> Logged by:          Robert Bengtsson
> Email address:      robert@fbt.se
> PostgreSQL version: PostgreSQL Data
> Operating system:   Windows Server 2003, Web Edition
> Description:        CREATE TABLE AS - may not supply table specification
> Details:
>
> CREATE TABLE AS seems to be broken.
>
> The following syntax works:
>
> CREATE TABLE l_modeltext WITHOUT OIDS AS (SELECT distinct modeltext as text,
> make, model, make_id, model_id from l_modelcode);
>
> while the following generates an error:
>
> CREATE TABLE l_modeltext
> (
>   id serial NOT NULL,
>   text varchar(60),
>   make varchar(30),
>   model varchar(30),
>   make_id int4,
>   model_id int4
> ) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id,
> model_id from l_modelcode);
>
> with the following errorcode:
>
> ERROR:  syntax error at or near "AS" at character 94

While you can specify the column names of the new table in CREATE TABLE
AS, you can not specify the column _types_ of the new columns:

       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
           [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
           AS query

The column types must match the column types of the original table.  The
fact that "AS" it targeted as the error location is expected, because it
is the place where the command is switched from CREATE TABLE to CREATE
TABLE AS.  See this:

    CREATE TABLE l_modeltext
    (
      id serial NOT NULL,
      text varchar(60),
      make varchar(30),
      model varchar(30),
      make_id int4,
      model_id int4
    ) AS SELECT 1;

"AS" is where I get the error message.

> However, the errorcode is, strangely enough, somewhat dependent on where the
> edit-cursor is placed inside pgAdmin.

That is strange.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073