Thread: column name order matters in insert into foo from bar

column name order matters in insert into foo from bar

From
"Grzegorz Jaśkiewicz"
Date:
hey,

it looks , as if when I have two tables, say
create table foo(
  a int,
  b varchar,
  c float
);

and :

create table bar(
  b varchar,
  a int,
  c float
);

migration of data from one to the other using

insert into foo select * from bar; will fail.
How can I walk around that please ?

--
GJ

Re: column name order matters in insert into foo from bar

From
"Grzegorz Jaśkiewicz"
Date:
to answer myself, and also make matter a bit more complicated.
the table has like 20 columns, so - yes, I can probably name all of
them one by one, but - is there any way to do it without need to name
all of them ?

Re: column name order matters in insert into foo from bar

From
"Pavel Stehule"
Date:
2008/12/4 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> to answer myself, and also make matter a bit more complicated.
> the table has like 20 columns, so - yes, I can probably name all of
> them one by one, but - is there any way to do it without need to name
> all of them ?
>

no,

regards
Pavel Stehule

p.s. you can try use stored procedure for generating these statements


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

Re: column name order matters in insert into foo from bar

From
"Grzegorz Jaśkiewicz"
Date:
On Thu, Dec 4, 2008 at 1:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> p.s. you can try use stored procedure for generating these statements
yeah, I ended up generating it using:
 select  array_to_string(ARRAY(select column_name::text  FROM
information_schema.columns where table_name='foo'), ',');

This was one time thing anyway, so than simple copy and paste, etc ..

thanks folks.

--
GJ