Re: 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