How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?

Question

How to add a new column in a table after the 2nd or 3rd column in the table using postgres?

My code looks as follows

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id 

Answer 1

No, there's no direct way to do that. And there's a reason for it - every query should list all the fields it needs in whatever order (and format etc) it needs them, thus making the order of the columns in one table insignificant.

If you really need to do that I can think of one workaround:

• dump and save the description of the table in question (using pg_dump --schema-only --table=<schema.table> ...)
• add the column you want where you want it in the saved definition
• rename the table in the saved definition so not to clash with the name of the old table when you attempt to create it
• create the new table using this definition
• populate the new table with the data from the old table using 'INSERT INTO <new_table> SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table>';
• rename the old table
• rename the new table to the original name
• eventually drop the old, renamed table after you make sure everything's alright