Specify INSERT columns with query I'd like to INSERT data from one table to another, where the destination table has all the source columns with a few others in between. The tables have a large number of columns (plus this can get repeated now and then), so I'd rather not explicitly write the column names.

Along the lines of (doesn't work):

INSERT INTO dest_table
(SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'source_table')
SELECT * from source_table


The context is that I'm trying to alter a column's position.
Altering a columns position is going to require a full table rewrite. My suggestion is not to alter column positions. However, there is an abundance of people doing this and there are numerous ways to do it. In my experience,


one way is far better and easier, if you have the privileges, and
most DBAs have those privileges.


My suggestion is to use pg_dump.


Dump with --column-inserts


  Dump data as INSERT commands with explicit column names (INSERT
  INTO table (column, ...) VALUES ...). This will make restoration
  very slow; it is mainly useful for making dumps that can be loaded
  into non-PostgreSQL databases. However, since this option generates
  a separate command for each row, an error in reloading a row causes
  only that row to be lost rather than the entire table contents.

Remove from dump what you don't need.
Modify table definition or ordering.
In a transaction,


Drop the old tables 
Execute the script creating the new tables.
Commit