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

From: Ole Tange <ole(at)tange(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: insert into test_b (select * from test_a) with different column order
Date: 2010-03-29 14:51:35
Message-ID: ce534faa1003290751g6552668fi675e1e42e7cfc525@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-03-29 14:52:20 Re: How to give security to pg_catalogs
Previous Message 赤松 建司 2010-03-29 14:43:20 help