2019-12-06

Introduction to SQL - Changing Database and Tables Structures

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 its consequences. 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?

We'll be using the tables created in this previous blog post to follow this section.

SQL ALTER TABLE

The ALTER TABLE statement adds, deletes, or modifies columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on a current table.

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

➜  ~ sudo su - postgres
[sudo] password for daniel: 
[email protected]:~$ 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=#

ADD Column

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 update each row (if each one has different data) or change in bulk, as we saw in earlier blog posts with the UPDATE statement.

ALTER/MODIFY a Column

Another standard 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 make 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' prefixes 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)

DROP COLUMN

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 learned a lot

Thanks for reading
Daniel Morales