How to add column before or after existing column

Question

It looks to me that Postgresql deosn't support to add column before or after existing column, and it only adds the column as the last position. Often I need to add a column, but I don't want to add it at last, I have to drop the table and recreate the table, and insert the data that the table already has.

I would ask if there is an appoach to add a column at the desired position.

Not sure if it helps, but check [this](https://stackoverflow.com/a/285740). I'm curious about why you want it in a specific order. — anupsabraham, Aug 07 '23 at 08:49
We have 4 columns, created_by, created_date, last_updated_by, last_updated_date.By convention, they are the last 4 columns, the other columns are business columns and before the 4 columns. — Tom, Aug 08 '23 at 05:32

score 2 · Answer 1 · answered Aug 07 '23 at 10:52

Use the CREATE TABLE statement to create a new table with the specified column order and the new column.

CREATE TABLE new_table (
    id serial PRIMARY KEY,
    new_column datatype,
    old_column1 datatype,
    old_column2 datatype
);

Copy data from the old table to the new table using INSERT INTO

INSERT INTO new_table (id, new_column, old_column1, old_column2)
SELECT id, new_column, old_column1, old_column2 FROM old_table;

Give the new table the name of the old table, and rename the old table to a temporary name.

ALTER TABLE old_table RENAME TO old_table_temp;
ALTER TABLE new_table RENAME TO old_table;

Thanks, currently, I am using this approach, which can' t be more ugly. — Tom, Aug 08 '23 at 05:29

How to add column before or after existing column

1 Answers1