Обсуждение: insert into test_b (select * from test_a) with different column order

I have 2 tables that have the same column names but in different
order. Similar to this:

  create table test_a (col_a text, col_b int);
  create table test_b (col_b int, col_a text);
  insert into test_a values ('abc', 2),( 'def', 3);

I would like to do this:

  insert into test_b (select * from test_a);

This fails because the columns in test_b are not in the same order as
test_a. For my use case the tables may get more columns or have
columns removed over time og be recreated in a different order, the
only thing that is given is that the column names in test_a and test_b
always are the same and that the datatype of the named columns are the
same.

Is there a general solution I can use to do the insert?

Regards,

Ole Tange

On Monday 29. March 2010 16.51.35 Ole Tange wrote:
> I have 2 tables that have the same column names but in different
> order. Similar to this:
>
>   create table test_a (col_a text, col_b int);
>   create table test_b (col_b int, col_a text);
>   insert into test_a values ('abc', 2),( 'def', 3);
>
> I would like to do this:
>
>   insert into test_b (select * from test_a);
>
> This fails because the columns in test_b are not in the same order as
> test_a. For my use case the tables may get more columns or have
> columns removed over time og be recreated in a different order, the
> only thing that is given is that the column names in test_a and test_b
> always are the same and that the datatype of the named columns are the
> same.
>
> Is there a general solution I can use to do the insert?

Per the SQL standard, there's no inherent order between columns. That said,
you'll usually get the columns in the order that they were created, but
there's no guarantee for it. Actually, when you do a SELECT * FROM ... you
make a totally unwarranted assumption that the columns will come out in any
specific order. So, the answer to your question is to specify the columns
explicitly in your query, as

insert into test_b (select col_b, col_a from test_a);

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/

On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:
> On Monday 29. March 2010 16.51.35 Ole Tange wrote:

>> I would like to do this:
>>
>>   insert into test_b (select * from test_a);
>
> Per the SQL standard, there's no inherent order between columns. That said,
> you'll usually get the columns in the order that they were created, but
> there's no guarantee for it.

And the create order in my case is (for all practical purposes) random.

> Actually, when you do a SELECT * FROM ... you
> make a totally unwarranted assumption that the columns will come out in any
> specific order.

I had hoped the INSERT would be intelligent enough to use the column
names and match on these.

> So, the answer to your question is to specify the columns
> explicitly in your query, as
>
> insert into test_b (select col_b, col_a from test_a);

This will not work for me as I do not know in advance what columns
exist in test_a or test_b. I only know they are called the same (and
have the same datatypes).

So is there a dynamic way in which I can generate the INSERT statement
given the name of the two tables?

Maybe something like listing all columns in test_b in the order that
test_b wants them and from this create the SELECT statement and
execute it?


/Ole