0

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.

Improve this question

1 Answer 1

Reset to default
2

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,

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

My suggestion is to use pg_dump.

  1. 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.

  2. Remove from dump what you don't need.

  3. Modify table definition or ordering.
  4. In a transaction,
    1. Drop the old tables
    2. Execute the script creating the new tables.
    3. Commit
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.