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

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

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

Looks like a bug to me too. Will look into it.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

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

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

Yes, it will be fixed in 7.2, which is the next release.

---------------------------------------------------------------------------

Thanks,
will this be fixed on the next release?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Sunday, November 04, 2001 9:02 PM
To: Command Prompt, Inc.; Jeff Lu; Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

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

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thanks,
will this be fixed on the next release?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Sunday, November 04, 2001 9:02 PM
To: Command Prompt, Inc.; Jeff Lu; Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

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

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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