Something we’ll need to modify is the structure of our tables inside the database. For instance. Let’s suppose that we decided to add a new column called address to our table Customers, so we need to modify the structure of the given table and the consequences of it. For instance, what will happen if we already have 1.000 records in that table? What would be the values for the column phone_number for those records? Something like nil? Some address by default? A city? 

To follow this section we’ll be using the tables created on this previous blog post

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Please enter to the database created here called newsletters and check the table customers

➜  ~ sudo su - postgres

[sudo] password for daniel: 

postgres@daniel-MS-7A15:~$ psql

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

Type "help" for help.

postgres=# \c newsletters

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

You are now connected to database "newsletters" as user "postgres".

newsletters=# \l

newsletters=# \d newsletters

Did not find any relation named "newsletters".

newsletters=# \d customers

                 Table "public.customers"

    Column     |  Type   | Collation | Nullable | Default 

---------------+---------+-----------+----------+---------

 customer_id   | integer |           | not null | 

 customer_name | text    |           | not null | 

 address       | text    |           | not null | 

Indexes:

    "customers_pkey" PRIMARY KEY, btree (customer_id)

newsletters=# 

Let’s add a new column to the table Customers with the following syntax

newsletters=# ALTER TABLE customers

newsletters-# ADD phone_number integer; 

ALTER TABLE

newsletters=# \d customers

                 Table "public.customers"

    Column     |  Type   | Collation | Nullable | Default 

---------------+---------+-----------+----------+---------

 customer_id   | integer |           | not null | 

 customer_name | text    |           | not null | 

 address       | text    |           | not null | 

 phone_number  | integer |           |          | 

Indexes:

    "customers_pkey" PRIMARY KEY, btree (customer_id)

newsletters=# 

But what happens with the current records?

newsletters=# SELECT * FROM customers;

 customer_id |   customer_name   |      address      | phone_number 

-------------+-------------------+-------------------+--------------

           1 | Allie Rahaim      | 123 Broadway      |             

           2 | Jacquline Diddle  | 456 Park Ave.     |             

           3 | Lizabeth Letsche  | 789 Main St.      |             

           4 | Jessia Butman     | 1 Columbus Ave.   |             

           5 | Inocencia Goyco   | 12 Amsterdam Ave. |             

           6 | Bethann Schraub   | 29 Monticello     |             

           7 | Janay Priolo      | 81 Harrisburg     |             

           8 | Ophelia Sturdnant | 31 Deerfield Ave. |             

           9 | Eryn Vilar        | 56 Morton St.     |             

          10 | Jina Farraj       | 100 Bryan Ave.    |             

(10 rows)

All data keeps as nil/null, so to fill out previous data you just need to start updating each row (if each one has different data) or change in bulk as we saw in previous blog posts with the UPDATE statement

Another common operation is to modify the data type of a column. For instance, we have created the column phone_number as an integer, but some people like to create phone numbers with a dash in the middle to identify the number, something like 301-3852-3659. Other phone numbers are internationals, so what people do here is to add the countries’ prefix like (1) 301-3852-3659. So let’s change the column phone_number to string

newsletters=# ALTER TABLE customers

newsletters=# ALTER COLUMN phone_number TYPE VARCHAR;

Now let’s check the structure

newsletters=# \d customers

                      Table "public.customers"

    Column     |       Type        | Collation | Nullable | Default 

---------------+-------------------+-----------+----------+---------

 customer_id   | integer           |           | not null | 

 customer_name | text              |           | not null | 

 address       | text              |           | not null | 

 phone_number  | character varying |           |          | 

Indexes:

    "customers_pkey" PRIMARY KEY, btree (customer_id)

The opposite operation is to delete a column from a table. To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

newsletters=# ALTER TABLE customers

newsletters-# DROP COLUMN phone_number;

ALTER TABLE

newsletters=# SELECT * FROM customers;

 customer_id |   customer_name   |      address      

-------------+-------------------+-------------------

           1 | Allie Rahaim      | 123 Broadway

           2 | Jacquline Diddle  | 456 Park Ave.

           3 | Lizabeth Letsche  | 789 Main St.

           4 | Jessia Butman     | 1 Columbus Ave.

           5 | Inocencia Goyco   | 12 Amsterdam Ave.

           6 | Bethann Schraub   | 29 Monticello

           7 | Janay Priolo      | 81 Harrisburg

           8 | Ophelia Sturdnant | 31 Deerfield Ave.

           9 | Eryn Vilar        | 56 Morton St.

          10 | Jina Farraj       | 100 Bryan Ave.

(10 rows)

As you can see, now we can deal with the structure of the tables and with this we’ll be finishing the introduction to SQL bundle blog posts.

Now we’re going to start with Rails!

I hope you learnt a lot

Thanks for reading

Daniel Morales