Re: Got error on CREATE TABLE AS (+Bug?)

Yes, I've tried the following and it worked but the unique key contraint is
losed.

CREATE TABLE employee_backup AS SELECT * FROM employee;

-----Original Message-----
From: Command Prompt, Inc. [mailto:pgsql-general@commandprompt.com]
Sent: Sunday, November 04, 2001 3:31 PM
To: Jeff Lu
Cc: Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)


On Sun, 4 Nov 2001, Jeff Lu wrote:
>Hi,
>I'm getting parser error at or near AS
>when try to copy a table using CREATE TABLE AS
>Here what I have in the sql statement
>CREATE TABLE employee_backup (id text UNIQUE, lastname text, firstname
text,
>phone text) AS SELECT * FROM employee
>Can some body tell me what is wrong with the sql statement?

I don't believe you can specify types or constraints. Also, if you specify
the name of each column in parens, PostgreSQL seems to get confused if you
do a SELECT * following AS, rather than specify each table name as well
(as of 7.1.x, I get ERROR:  CREATE TABLE/AS SELECT has mismatched column
count - is this a known bug?)

I believe this should work, though you lose the UNIQUE constraint:

CREATE TABLE employee_backup AS SELECT * FROM employee;


Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com