BUG #10674: syntax error with CREATE TABLE table AS SELECT (column1, column 2) FROM table2

Started by Nonamealmost 12 years ago2 messagesbugs
Jump to latest
#1Noname
professor@gmail.com

The following bug has been logged on the website:

Bug reference: 10674
Logged by: Todd Sedano
Email address: professor@gmail.com
PostgreSQL version: 9.3.3
Operating system: Mac OS 10.8.5
Description:

Note: I feel like I must be doing something wrong, but I've been able to
reproduce this in two very different environments.

The following SQL is valid postgres, see
http://www.postgresql.org/docs/9.3/static/sql-createtableas.html
"CREATE TABLE retail_demo.imported_table AS SELECT (column1, column2) FROM
retail_demo.external_table;"

However, it generates the odd message "ERROR: column "row" has pseudo-type
record"

We were able to get around this issue, by removing the () around (column1,
column2)
"CREATE TABLE retail_demo.imported_table AS SELECT column1, column2 FROM
retail_demo.external_table;"

This is reproducible with this sequence....

create table source_table (column1 text, column2 text, column3 text);
insert into source_table values ('value 1', 'value 2', 'value 3');
create table destination_table AS SELECT (column1, column2) from
source_table;
ERROR: column "row" has pseudo-type record
create table destination_table AS SELECT column1, column2 from source_table;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#2Michael Paquier
michael@paquier.xyz
In reply to: Noname (#1)
Re: BUG #10674: syntax error with CREATE TABLE table AS SELECT (column1, column 2) FROM table2

On Tue, Jun 17, 2014 at 7:03 AM, <professor@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 10674
Logged by: Todd Sedano
Email address: professor@gmail.com
PostgreSQL version: 9.3.3
Operating system: Mac OS 10.8.5
Description:

Note: I feel like I must be doing something wrong, but I've been able to
reproduce this in two very different environments.

The following SQL is valid postgres, see
http://www.postgresql.org/docs/9.3/static/sql-createtableas.html
"CREATE TABLE retail_demo.imported_table AS SELECT (column1, column2) FROM
retail_demo.external_table;"
However, it generates the odd message "ERROR: column "row" has pseudo-type
record"

This is not a bug. Writing a list of columns within parentheses in a
SELECT list represents them as a row (this is valid SQL).

We were able to get around this issue, by removing the () around (column1,
column2)
"CREATE TABLE retail_demo.imported_table AS SELECT column1, column2 FROM
retail_demo.external_table;"

And you are right, simply remove the parentheses in the SELECT list
and the columns will be selected one by one, which is what you are
looking for.
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs