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

On Sun, 4 Nov 2001, Command Prompt, Inc. wrote:
>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?)

Er, make that "column name" above, not "table name". Here's an example of
what I was trying to describe:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR:  CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

You can explicitly provide each column name in the AS SELECT clause, and
that appears to work properly, but supplying a "*" confuses the backend.

Is this as it should be, or is it a known issue?

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