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 follow

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 daved 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

Answer 2

The order of the columns is totally irrelevant in relational databases

Yes.

For instance if you use Python, you would do :

cursor.execute( "SELECT id, name FROM users" )
for id, name in cursor:
    print id, name

Or you would do :

cursor.execute( "SELECT * FROM users" )
for row in cursor:
    print row['id'], row['name']

But no sane person would ever use positional results like this :

cursor.execute( "SELECT * FROM users" )
for id, name in cursor:
   print id, name