How to Insert a Column in PostgreSQL

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;

For example:

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 id and customer_name?

I’ve checked StackOverflow and psql documentation. So sure that there is no way to insert a column in the middle.

Before we do it, we have to know how to verify that our solution is good.

\d current table
select count(id)
\d reference table
select count(ref_id)
\d reference table
select count(ref_id)
check views
NB: check all triggers to see if there is a select * from currentTable statement. If there is, we also need to back up and rebuild this table.

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
1. We need to reuse definition while creating new customers table
2. 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
```

My processes:

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);