Add database table column with default index as last

0
votes

I want to add a column in a table of a databse such that the column always remain as the last column even if the new columns are added later. example:

Name, Id, Address

I need to add column timestamp which will be done by alter command and it will add column at the last index . So after adding timestamp column

Name, Id, Address, timestamp

Now suppose in future if a new requirement came to add new column phone_number an alter command will be run which will add phone_number after timestamp column

Name, Id, Address, timestamp, phone_number

If we want to add phone_number before timestamp we can use AFTER clause and can add phone_number after Address,so it will become

Name, Id, Address, phone_number, timestamp

but suppose some users forget to use the AFTER clause which will place the phone_number after timestamp and hence will break the logic which says the timestamp must be as the last column.

So is there any way to keep the timestamp column at the last always ?

1
databasepostgresql
Where did you find AFTER clause in Postgres?,If we want to add phone_number before timestamp we can use AFTER clause, what your saying??.You must thoroughly read ALTER TABLE - Vivek S.
You can't add a new column at a specific position in Postgres. A new column is always added at the end. So the answer to your question is: "No" - a_horse_with_no_name
link Look for after clause in alter command. - Jerry
@IranjeetSingh do you know what's the difference between MySQL and Postgres ?. Postgres IS NOT TO MySQL - Vivek S.
Rule of RDBMS: there is no order. Not for rows, not for columns. - joop

1 Answers

1
votes

This is not supported by PostgreSQL, at least in 9.5 and older. You cannot set the order of columns.

You're not really supposed to care - columns are meant to be referenced by name, not ordinal position. That's the purist approach, and there are times it'd be nice to use ordinal positions. You still can, you just can't change those positions except by dropping and re-creating the table.

You just need to change your application logic so it gets the timestamp by column name.