[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] insert into test_b (select * from test_a) with different column order
On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] insert into test_b (select * from test_a) with different column order
> > > 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? > > You can write a procedure e.g. in pl/pgsql that will check the column names from a system view like pg_* (I don't remember now) and create the query from the column names and some sql keywords into a text variable. Later you can use EXECUTE for executing such a query from a variable. regards Szymon Guz