Alter a Table

If you don't already have a database named "library", then make sure to run createdb libraryfrom the terminal before starting this chapter. The library database is needed to work through this chapter's content. The library database can be restored from a backup file. First download the backup file for this chapter from here.

Then run the following command: psql -d library < alter_table.sql

Times change, needs change, and so do the requirements of your database. It may be the case that you need to alter a column's name, add a new column, change the type of a column, or remove it altogether.

Altering columns

It is important to consider how schema changes will affect the data in a table. Adding an extra column to a table will add an extra column to all existing rows, just as it would in a spreadsheet. Deleting a column means all data in that column will be lost forever. Before jumping in to alter your table, take extra time to think about how the change will affect your data.

Existing tables can be altered with an ALTER TABLE statement. Again, this is for altering a table schema only. To change its data, you would use the UPDATE statement we saw in the last chapter.

The basic format of an ALTER TABLE statement is:

ALTER TABLE table_to_change HOW TO CHANGE THE TABLE additional arguments;

So, for example:

ALTER TABLE users ADD COLUMN last_login timestamp NOT NULL DEFAULT NOW();

Let's break that down:

ALTER TABLE                                 -- this statement alters
users                                       -- the table users
ADD COLUMN                                  -- by adding a column
last_login timestamp NOT NULL DEFAULT NOW() -- using these options

Let's look at what this specific statement does a bit more closely.

Adding a column

If you need to add a column to the table you've created, one that was not specified in the original schema, use an ADD COLUMN clause in an ALTER TABLE statement.

Run this command in your psql console and follow along to see how the database changes.

ALTER TABLE users ADD COLUMN last_login timestamp NOT NULL DEFAULT NOW();

The command above adds a new column to the users table with the name last_login, a datatype of timestamp and a default value based on the function NOW(). Let's see what our table looks like with the new column by running the meta command \d and using the table name as an argument:

library=# \d users
                                     Table "public.users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 username   | character(25)               | not null
 enabled    | boolean                     | default true
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
  • timestamp: So far we have seen CHAR and INT datatypes. As the name implies, timestamp, is a datatype that allows storage of date and time. The following is an example of timestamp data: '2005-11-22 05:16:45'
  • NOW() is another SQL FUNCTION. It provides the current date and time when it is called. There are many such functions available and we will look at some of the common ones in subsequent chapters.

You might be wondering what our data looks like after this change and what got inserted in the new field last_login that was just added. Run the select statement below to view the records. The new column appears with the date and time in the records of when the change was made to the table.

  • Note: Your data will differ from the below data since your date and time will be different.
    library=# SELECT * FROM users;
     id |         username          | enabled |         last_login
    ----+---------------------------+---------+----------------------------
      1 | John Smith                | t       | 2016-03-20 20:47:32.941109
      2 | Jack Wilson               | t       | 2016-03-20 20:47:32.941109
      3 | Jack Smith                | f       | 2016-03-20 20:47:32.941109
    (3 rows)
    

Renaming a column

Sometimes you may need to rename a column. For example, we have been storing full names in the username column, and we may decide to rename it to full_name. You can rename a column using an ALTER TABLE statement in a similar way to how columns are added, only using a RENAME COLUMN clause:

library=# ALTER TABLE users RENAME COLUMN username TO full_name;
ALTER TABLE

Changing a column's datatype

It's also possible to change a column's datatype using an ALTER TABLE statement. We'll use this in conjunction with the ALTER COLUMN to target a specific column for change. In the statement below the datatype of username is changed from CHAR(25) to VARCHAR(25).

library=# ALTER TABLE users ALTER COLUMN full_name TYPE VARCHAR(25);
ALTER TABLE
  • VARCHAR: This is a new datatype we are encountering. It is short for Variable Length Characters. With the VARCHAR datatype, the database can store the data in a more efficient way then it can with the CHAR datatype. CHAR will always use up all allocated space; so, if we are using CHAR(25), and we add a word with 5 characters to that database column, trailing whitespace will be added for the extra 20 characters that are left. VARCHAR only uses the space it needs; if we add in a word of 5 characters to a column of datatype VARCHAR(25), only those 5 characters will be inserted into the table, with no trailing whitespace. VARCHAR, tends to be more popular with database administrators.

Now when we describe users, we have a field full_name with type VARCHAR(25).

library=# \d users
                                     Table "public.users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 full_name  | character varying(25)       | not null
 enabled    | boolean                     | default true
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Earlier we briefly mentioned that the ALTER COLUMN command requires the datatype. However, if you specify a datatype it has to be such that is applicable to all the records that are currently stored in that table. If the datatype is not fitting, SQL will throw an error, and the column will remain unchanged.

library=# ALTER TABLE users ALTER COLUMN full_name TYPE VARCHAR(5);
ERROR:  value too long for type character varying(5)

If you look at the data, nothing's changed. The data remained the same.

library=# SELECT * FROM users;
 id |  full_name  | enabled |         last_login
----+-------------+---------+----------------------------
  1 | John Smith  | t       | 2016-03-20 20:47:32.941109
  2 | Jack Wilson | t       | 2016-03-20 20:47:32.941109
  3 | Jack Smith  | f       | 2016-03-20 20:47:32.941109
(3 rows)

Removing a column

We can also remove a column. The command to remove a column from a table also uses ALTER TABLE.

Caution: This action is not reversible, so use with care.

library=# ALTER TABLE users DROP COLUMN enabled;
ALTER TABLE

library=# \d users
                                     Table "public.users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 full_name  | character varying(25)       | not null
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Now the enabled field has been removed, all data about the field has also been deleted.

library=# SELECT * FROM users;
 id |  full_name  |         last_login
----+-------------+----------------------------
  1 | John Smith  | 2016-03-20 20:47:32.941109
  2 | Jack Wilson | 2016-03-20 20:47:32.941109
  3 | Jack Smith  | 2016-03-20 20:47:32.941109
(3 rows)

Renaming and Deleting Tables

In the above section we looked at how to change the table structure by adding, modifying columns. We can also modify the table name or drop a table from the database.

Rename a table

A table can be renamed using the RENAME command we saw earlier. Running the statement and command below, you will see that the users table has been dropped, since relation users was not found when we tried to describe the table users.

library=# ALTER TABLE users RENAME TO all_users;
ALTER TABLE

library=# \d users
Did not find any relation named "users".

Now, if you describe the new table, can view the details of what used to be called the users table

library=# \d all_users
                                   Table "public.all_users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 full_name  | character varying(25)       | not null
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Deleting a Table

Deleting a Table has a straightforward command and the syntax for deleting a table is much like the command for dropping a database, DROP. Run the statement and command below on your psql console. If you try to describe the deleted table, you will get an error.

library=# DROP TABLE all_users;
DROP TABLE
library=# \d all_users
Did not find any relation named "all_users".

Summary

Let's quickly recap again:

Command Notes
ALTER TABLE users ADD COLUMN last_login timestamp NOT NULL DEFAULT NOW(); Alters the table users by adding column last_login with a datatype of timestamp, and with a default value of the function NOW based on when the record gets inserted
ALTER TABLE users RENAME COLUMN username TO full_name; Alters the table users by renaming the column username to full_name
ALTER TABLE users ALTER COLUMN full_name TYPE VARCHAR(15); Alters the table users by changing the datatype of column full_name to VARCHAR(15)
ALTER TABLE users DROP COLUMN enabled Alters the table users by deleting the column enabled
ALTER TABLE users RENAME TO all_users; Rename the table users to all_users
DROP TABLE all_users; permanently deletes the table all_users from the database library

We have now covered most of the basic SQL commands. Let's review these in the next chapter and also go over some database concepts.