Insert (not add) a column in PostgreSQL is quite a mission.
Add a column in a table
Add a column in a table in PostgreSQL is:
ALTER TABLE table_name ADD new_column_name column_definition;
CREATE TABLE customers ( id SERIAL PRIMARY KEY, customer_name VARCHAR NOT NULL );
ALTER TABLE customers ADD COLUMN phone VARCHAR;
It’s simple. I won’t go further. The question is what if I want to insert the column between
I’ve checked StackOverflow and psql documentation. So sure that there is no way to insert a column in the middle.
How to test
Before we do it, we have to know how to verify that our solution is good.
- Current table no data lost
- Current table no table definition change
- Other tables don’t lose data
- Other tables don’t have definition change
Before running our migration script
\d current table
\d reference table
\d reference table
- check views
- NB: check all triggers to see if there is a
select * from currentTablestatement. If there is, we also need to back up and rebuild this table.
Export schema and table definition
We need the schema definition because we want to compare customers related tables definitions are not changed.
pg_dump -U postgres -s myDatabase -n my_schema > my_schema_dump.txt
We need the customers definition because
- We need to reuse definition while creating new customers table
- we want to make sure new customers table remains the same definition
pg_dump -U postgres -s myDatabase -n my_schema -t my_schema.customers > customers_dump.txt
Insert a column to a table
- Back up customers table, copy all data to origin_customers table.
- Drop the current table.
- Rebuild customers table without constraints.
- Copy data back from origin_customers. Give your new column a default value or leave null.
- Add constrains back to the customers table.
- Add constrains back to reference tables.
Rebuild customers table sequence. Remember, the sequence cannot be 0.
select setval('customers_customers_id_seq', (select max(customers_id) from customers where customers_id > 0), true);
- Verify based on the testing plan.