On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen <leif@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
