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

Robert Bengtsson wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2100
> Logged by: Robert Bengtsson
> Email address: robert(at)fbt(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073