Alter a Table

Times change, needs change, and so do the requirements of your database. In the previous chapter we looked at how to create a new table in our database. There may be situations, however, where you need to alter an existing table's definition in some way. It may be the case that you need to alter a column's name, add a new column, change the data type of a column, or remove the table altogether. DDL provides a way for you to make such changes.

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 existing data.

Alter Table Syntax

Existing tables can be altered with an ALTER TABLE statement. An ALTER TABLE statement is part of DDL, and is for altering a table schema only; we'll look at updating data in a table later in this book.

The basic format of an ALTER TABLE statement is:

ALTER TABLE table_to_change HOW TO CHANGE THE TABLE additional arguments

In a moment we'll work through some specific examples of using the ALTER TABLE statement, but first let's remind ourselves of what our table currently looks like. Remember that within the psql console we can use the meta-command \d followed by a table name to describe that table. Let's do that now for our users table.

sql_book=# \d users
        Table "public.users"
  Column  |     Type      |  Modifiers
----------+---------------+----------------------------------------------------
 id       | integer       | not null default nextval('users_id_seq'::regclass)
 username | character(25) |
 enabled  | boolean       | default true
Indexes:
    "users_id_key" UNIQUE CONSTRAINT, btree (id)

Renaming a Table

One example of a change we might want to make to a table is to rename the table itself. A table can be renamed using the RENAME clause. We then need to specify what we want to rename the table to using the TO clause followed by the new name. Let's rename our users table to all_users.

sql_book=# ALTER TABLE users
sql_book-# RENAME TO all_users;
ALTER TABLE

On the first line above we run our SQL statement. The ALTER TABLE on the second line is the response we receive. This response is pretty generic; other than telling us our statement was executed without error, it doesn't really tell us what's changed. If we now try to describe our users table however, we get the following response.

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

PostgreSQL is unable to find a relation (i.e. table) called users since it has been renamed. If we use \d all_users instead, we can see the description of what used to be called the users table; all that's changed is the name of the table.

sql_book=# \d all_users
        Table "public.all_users"
  Column  |     Type      |  Modifiers
----------+---------------+----------------------------------------------------
 id       | integer       | not null default nextval('users_id_seq'::regclass)
 username | character(25) |
 enabled  | boolean       | default true
Indexes:
   "users_id_key" UNIQUE CONSTRAINT, btree (id)

Renaming a Column

As well as renaming an entire table, you can also use the RENAME clause to rename a specific column within the table. For example, we have been storing full names in the username column, and we may decide to rename it to full_name. The syntax is very similar to that for renaming a table, except that in between the RENAME and TO clauses, we need to use the COLUMN clause, passing it the name of the column we want to change.

sql_book=# ALTER TABLE all_users
sql_book-# RENAME COLUMN username TO full_name;
ALTER TABLE

Again we receive the generic ALTER TABLE response, but can use \d all_users to see that the column name has been changed.

sql_book=# \d all_users
        Table "public.all_users"
  Column   |     Type      |  Modifiers
-----------+---------------+----------------------------------------------------
 id        | integer       | not null default nextval('users_id_seq'::regclass)
 full_name | character(25) |
 enabled   | boolean       | default true
Indexes:
   "users_id_key" UNIQUE CONSTRAINT, btree (id)

Changing a Column's Datatype

Remember when we created our table, there were two mandatory pieces of information that we needed to supply about each column: name and data type. We've already seen how to change a column's name, but there may also be situations where you need to change the column's data type. We again do this by using an ALTER TABLE statement, but this time in conjunction with ALTER COLUMN to target a specific column for change. In the statement below, the datatype of full_name is changed from CHAR(25) to VARCHAR(25).

sql_book=# ALTER TABLE all_users
sql_book-# ALTER COLUMN full_name TYPE VARCHAR(25);
ALTER TABLE

Now when we describe all_users, we have a field username with type VARCHAR(25).

sql_book=# \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) |
  enabled   | boolean               | default true
Indexes:
  "users_id_key" UNIQUE CONSTRAINT, btree (id)

Adding a Constraint

When we created our table, we explained that column names and data types are a required part of each column definition, whereas constraints are optional. If we want to change a column's name or data type we need to alter the existing definition for those things. Constraints are a little different in that rather than changing them we add them to, or remove them from, the column definition (note: there is an ALTER CONSTRAINT clause that can be used to change certain aspects of Foreign Key constraints, but most of the time you will be adding or removing constraints). Another difference with constraints is that whereas a column can only have one name and one data type, it can have more than one constraint.

Our id column in our all_users table already has a NOT NULL constraint, which means that a null value cannot be input to that column as part of a database record. It would be useful to apply this same safeguard to our full_name column. That column currently does not have a NOT NULL constraint; let's fix that.

The syntax for adding constraints can vary depending on the type of constraint we're adding. Some types of constraint are considered 'table constraints' (even if they apply to a specific column) and others, such as NOT NULL and DEFAULT are considered 'column constraints'.

The form for adding a column constraint is as follows:

ALTER TABLE table_name ALTER COLUMN column_name SET constraint clause

Whereas the form for adding a table constraint is:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint clause

We'll look a bit more closely at this second form later in the book, for now let's add our NOT NULL constraint to our full_name column.

sql_book=# ALTER TABLE all_users
sql_book-# ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE

Let's check what our table looks like after executing this statement.

sql_book=# \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
  enabled   | boolean               | default true
Indexes:
  "users_id_key" UNIQUE CONSTRAINT, btree (id)

Removing a Constraint

Just as we can add constraints to a table after creating it, we may also remove them. The syntax for removing a constraint also has a couple of forms, depending on the type of constraint.

The form for removing a column constraint is as follows:

ALTER TABLE table_name ALTER COLUMN column_name DROP CONSTRAINT

Whereas to remove a table constraint the form is:

ALTER TABLE table_name DROP CONSTRAINT constraint_name

When we used a specified data type of serial for our id column, this automatically added a DEFAULT constraint that uses nextval function to set id to the next available value if no value is specified. This is quite a useful set-up for our id column, as we'll see later when we start adding data to our table. If we did want to remove the DEFAULT constraint on the id column we could do it like this:

sql_book=# ALTER TABLE all_users
sql_book-# ALTER COLUMN id DROP DEFAULT;

After executing this statement, our table should look like this:

sql_book=# \d all_users
                Table "public.all_users"
   Column   |          Type         |    Modifiers
------------+-----------------------+-----------------------------
 id         | integer               | not null
 full_name  | character varying(25) | not null
 enabled    | boolean               | default true
Indexes:
   "users_id_key" UNIQUE CONSTRAINT, btree (id)

Adding a Column

So far we've looked at changing the definition of existing columns in our table. There may also be situations where you need to add an entirely new column. The product manager for our application has decided that we need to record the last login time for all of our users; let's meet this requirement by adding a last_login column to our all_users table.

If you need to add a column to the table you've created, one that was not specified in the original schema, you can 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 all_users
  ADD COLUMN last_login timestamp NOT NULL DEFAULT NOW();

The way we define a column when adding a new column to a table is essentially the same as the way we define a column when creating a table: we need to define a column name, a data type, and any optional constraints. The command above adds a new column to the all_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:

sql_book=# \d all_users
                  Table "public.all_users"
   Column   |            Type             |     Modifiers
------------+-----------------------------+----------------------------
 id         | integer                     | not null
 full_name  | character varying(25)       | not null
 enabled    | boolean                     | default true
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_id_key" UNIQUE CONSTRAINT, btree (id)

NOW() is an 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.

Removing a Column

Just as we can add new columns to existing tables, we also remove columns. The command to remove a column from a table also uses the ALTER TABLE clause. Say for example we wanted to remove the enabled column from the all_users table.

sql_book=# ALTER TABLE all_users DROP COLUMN enabled;
ALTER TABLE

If we describe our table now, we can see that there are only three columns, and enabled is not one of them.

sql_book=# \d all_users
                  Table "public.all_users"
   Column   |            Type             |     Modifiers
------------+-----------------------------+----------------------------
 id         | integer                     | not null
 full_name  | character varying(25)       | not null
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_id_key" UNIQUE CONSTRAINT, btree (id)

Dropping Tables

As well as renaming a table, altering the columns within it, and adding or removing columns, we can also remove a table from our database completely.

Deleting a table has a relatively 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.

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

Caution: Actions such as DROP COLUMN and DROP TABLE are not reversible. Currently we don't have any data in our table, but if we did and we performed one of these actions, we would have lost all of the data in that table or column. Always perform such actions with care.

Summary

Whoa! We covered a lot of ground in this chapter! We've explored the general syntax for ALTER TABLE and also looked at numerous ways in which we can alter an existing table:

  • Renaming a table
  • Renaming a column
  • Changing a column's data type
  • Adding a constraint
  • Removing a constraint
  • Adding a column
  • Removing a column
  • Dropping a table

Although the SQL statements for all of these actions use the same initial ALTER TABLE clause, the specific syntax for each varies according to the action. Let's quickly recap:

Action Command Notes
Add a column to a table ALTER TABLE table_name ADD COLUMN column_name data_type CONSTRAINTS; Alters a table by adding a column with a specified data type and optional constraints.
Alter a column's data type ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type; Alters the table by changing the datatype of column.
Rename a table ALTER TABLE table_name RENAME TO new_table_name; Changes the name of a table in the currently connected to database.
Rename a column within a table ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; Renames a column of the specified table.
Add column constraint ALTER TABLE table_name ALTER COLUMN column_name SET constraint clause; Adds a specified constraint to the specified table column.
Add table constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint clause; Adds a specified constraint to the specified table.
Remove a column constraint ALTER TABLE table_name ALTER COLUMN column_name DROP CONSTRAINT; Removes a constraint from the specified table.
Remove a table constraint ALTER TABLE table_name DROP CONSTRAINT constraint_name; Removes a constraint from the specified table.
Remove a column from a table ALTER TABLE table_name DROP COLUMN column_name Removes a column from the specified table.
Delete a table from the database DROP TABLE table_name; Permanently deletes the specified table from its database.

In this section of the book we covered how to change the structure and foundation of our database and its relations. Having the database structure in place is only part of the story though; as explained earlier the schema and data work together to provide us with the structured information that we require from our database. In the next section we'll focus on using that new structure to manage data within our database, learning how to select, add, update, and delete data as needed.

Before moving onto the next section, make sure that you have a clear understanding the topics we covered in this one at a conceptual level. You probably don't need to memorize all of the specific syntax we've covered in this set of chapters. The syntax for DDL is generally only used at the outset when first creating your database and its tables, which is much less often than when you actually work with the data in those tables.

As long as you have a clear picture of how schema works, you can always refer back to this book or to the official documentation if you need to check on a particular piece of syntax. Over the next few chapters however, you should familiarize yourself as much as possible with the DML syntax which we will cover. The bulk of the time you spend working with databases will be spent manipulating data, so it is important to be as fluent as possible with the relevant commands and clauses.

Exercises

  1. Make sure you are connected to the encyclopedia database. Rename the famous_people table to celebrities.

    Solution

    ALTER TABLE famous_people RENAME TO celebrities;
    
  2. Change the name of the name column in the celebrities table to first_name, and change its data type to varchar(80).

    Solution

    Here we need to use two separate SQL statments:

    ALTER TABLE celebrities
    RENAME COLUMN name TO first_name;
    
    ALTER TABLE celebrities
    ALTER COLUMN first_name TYPE VARCHAR(80);
    

    The order in which you execute the statements isn't important, but if you change the column name first then you need to reference the column by its new name when changing the data type.

    Note: most actions to alter a table can be combined into a single statement, but this is not possible with certain actions (including RENAME).

  3. Create a new column in the celebrities table called last_name. It should be able to hold strings of lengths up to 100 characters. This column should always hold a value.

    Solution

    ALTER TABLE celebrities
    ADD COLUMN last_name varchar(100) NOT NULL;
    
  4. Change the celebrities table so that the date_of_birth column uses a data type that holds an actual date value rather than a string. Also ensure that this column must hold a value.

    When converting data types, if there is no implicit conversion from the old type to the new type you need to add a USING clause to the statement, with an expression that specifies how to compute the new column value from the old. Sometimes these expressions can be quite complex, but a simple form would be something like:

    ALTER COLUMN column_name
    TYPE new_data_type
    USING column_name::new_data_type
    

    Solution

    ALTER TABLE celebrities
      ALTER COLUMN date_of_birth TYPE date
        USING date_of_birth::date,
      ALTER COLUMN date_of_birth SET NOT NULL;
    

    Here, we could either execute two separate statements, or use a single statement which combines the actions. Unlike our earlier example with RENAME, multiple, comma-separated, ALTER COLUMN actions can be combined into a one ALTER TABLE statement.

    We also need to add the USING clause to our statement altering the column's data type, since there is no implicit conversion (or cast) from the varchar data type to the date data type.

  5. Change the max_weight_kg column in the animals table so that it can hold values in the range 0.0001kg to 200,000kg

    Solution

    ALTER TABLE animals
      ALTER COLUMN max_weight_kg TYPE DECIMAL(10,4);
    

    Although the data type of the column is remaining as decimal, we need to change the precision and scale in order to allow two more digits overall and one more digit after the decimal point. The syntax to do this is the same as if we were completely changing the data type of the column.

  6. Change the animals table so that the binomial_name column cannot contain duplicate values.

    You will need to use the table constraint form and specify a particular column as part of the constraint clause:

    ALTER TABLE table_name
      ADD CONSTRAINT constraint_name CONSTRAINT_TYPE (column_name);
    

    Solution

    In order to do this we can add a UNIQUE constraint to that column.

    ALTER TABLE animals
      ADD CONSTRAINT unique_binomial_name UNIQUE (binomial_name);
    
  7. Connect to the ls_burger database. Add the following columns to the orders table:

    • A column called customer_email; it should hold strings of up to 50 characters.
    • A column called customer_loyalty_points that should hold integer values. If no value is specified for this column, then a value of 0 should be applied.

    Solution

    ALTER TABLE orders
      ADD COLUMN customer_email varchar(50),
      ADD COLUMN customer_loyalty_points integer DEFAULT 0;
    
  8. Add three columns to the orders table called burger_cost, side_cost, and drink_cost to hold monetary values in dollars and cents (assume that all values will be less than $100). If no value is entered for these columns, a value of 0 dollars should be used.

    Solution

    ALTER TABLE orders
      ADD COLUMN burger_cost decimal(4,2) DEFAULT 0,
      ADD COLUMN side_cost decimal(4,2) DEFAULT 0,
      ADD COLUMN drink_cost decimal(4,2) DEFAULT 0;
    

    It doesn't really matter whether we set the DEFAULT here as 0 or 0.00, since PostrgeSQL will evaluate it as 0.00 either way due to the scale we have set for the decimal data type.

  9. Remove the order_total column from the orders table.

    Solution

    ALTER TABLE orders DROP COLUMN order_total;