Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Robert Bengtsson <robert(at)fbt(dot)se>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2100: CREATE TABLE AS - may not supply table specification
Date: 2005-12-09 17:18:49
Message-ID: 200512091718.jB9HInO16264@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: alejandro ramirezDate: 2005-12-09 18:09:52
Subject: BUG #2105: ERROR: relation "comoma" does not exist
Previous:From: Tom LaneDate: 2005-12-09 17:10:15
Subject: Re: BUG #2101: Postmaster Crash Using PL/PgSQL With %TYPE on Non-Exisiting Column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group