Insert (not add) a column in PostgreSQL is quit 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 to a table.
How to test
Before we do it, we have to know how to verify that our solution is good.
- Current table Not data lost
- Current table o 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 -d 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 -d 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, 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.