Re: insert into test_b (select * from test_a) with different column order

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/