I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases:
DB1.schema1.table1 has these columns:
name_pk | date | type
DB2.schema1.table1 has these:
name_pk | date | value | type
I need to add
value in the first database so that I can dump its data and restore it to DB3 (along with DB2 data, DB3 has the same columns than DB2).
What would be the best way to solve this? I thought:
- Just add the "value" column to DB1 before dumping the data. However, because PostgreSQL doesn't have
INSERT ... AFTERclause, the column gets added to the end, which is not good.
- Dumping the table with schema-only into
schema.sql, editing the resulting file manually and adding the missing column to the right spot. It does the trick, but it requires manual work and not suitable for my script going through multiple databases.
- Adding column to the end, then adding the rest columns after that and deleting the ones before it. This requires finding out the names and types of the rest of the columns.
- Is there any other way of doing this easily?