7.9 Modifying Objects

After objects like tables have been created, it might be necessary to modify those objects. Therefore PostgreSQL provides some simple commands, which you will learn about in this section.

7.9.1 Modifying Tables

Most modifications can be done with the command UPDATE and ALTER. You have already learned about UPDATE in this chapter, and now it is time to have a closer look at the ALTER command. Many activities, such as modifying users, groups, and tables, can be done with ALTER. In this section you will focus on ALTER TABLE. Let's look at the syntax overview of the command:

 phpbook=# \h ALTER TABLE Command:     ALTER TABLE Description: change the definition of a table Syntax: ALTER TABLE [ ONLY ] table [ * ]     ADD [ COLUMN ] column type [ column_constraint [ ... ] ] ALTER TABLE [ ONLY ] table [ * ]     ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } ALTER TABLE [ ONLY ] table [ * ]     ALTER [ COLUMN ] column SET STATISTICS integer ALTER TABLE [ ONLY ] table [ * ]     RENAME [ COLUMN ] column TO newcolumn ALTER TABLE table     RENAME TO newtable ALTER TABLE table     ADD table constraint definition ALTER TABLE [ ONLY ] table         DROP CONSTRAINT constraint { RESTRICT | CASCADE } ALTER TABLE table         OWNER TO new owner 

As you can see, the command is powerful and provides a lot of features.

One of the most important things you can do with the help of ALTER TABLE is to add columns to a table. Let's look at the table called student:

 phpbook=# \d student        Table "student"  Column |  Type   | Modifiers --------+---------+-----------  id     | integer |  name   | text    | Indexes: idx_student_id 

The table contains two columns. To add a third column for storing the birthday, use ALTER TABLE:

 phpbook=# ALTER TABLE student ADD COLUMN birthday timestamp; ALTER 

If no error occurred, the column has successfully been added to the table:

 phpbook=# \d student                   Table "student"   Column  |            Type             | Modifiers ----------+-----------------------------+-----------  id       | integer                     |  name     | text                        |  birthday | timestamp(6) with time zone | Indexes: idx_student_id 

The table contains three columns now. To modify the name of the column you have just added, ALTER TABLE can be used again:

 phpbook=# ALTER TABLE student RENAME COLUMN birthday TO day_of_birth; ALTER 

day_of_birth will be the new name of the column:

 phpbook=# \d student                     Table "student"     Column    |            Type             | Modifiers --------------+-----------------------------+-----------  id           | integer                     |  name         | text                        |  day_of_birth | timestamp(6) with time zone | Indexes: idx_student_id 

Sometimes it is necessary to change the name of a table. In general, changing the name of a table should be avoided because this will affect your entire application. However, it is necessary to know how the name of a table can be changed. Let's look at the table whose name you want to change:

 phpbook=# \d course           Table "course"    Column   |  Type   | Modifiers ------------+---------+-----------  student_id | integer |  course     | text    | 

The name has to be training_course instead of course. Using a simple command will change the name to the desired string.

 phpbook=# ALTER TABLE course RENAME TO training_course; ALTER 

The old relation does not exist any more and a new name has been assigned to the table:

 phpbook=# \d course Did not find any relation named "course". phpbook=# \d training_course      Table "training_course"    Column   |  Type   | Modifiers ------------+---------+-----------  student_id | integer |  course     | text    | 

7.9.2 Modifying Sequences

Sequences are an essential feature of every sophisticated database. Sequences can be used to generate consecutively numbered lists and many other things. Normally, sequences are used to generate a list of unique numbers. In some cases, however, it is necessary to modify the current value of a sequence.

Let's create a sequence and see how this can be done:

 phpbook=# CREATE SEQUENCE seq_showseq INCREMENT 1 START 1; CREATE 

The start of the sequence has been set to 1. To retrieve information about a sequence, it can be accessed just like a table by using SELECT:

 phpbook=# SELECT * FROM seq_showseq;  sequence_name | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+- ------------+---------+-----------+-----------  seq_showseq   |          1 |            1 | 9223372036854775807 |         1 |           1 | 1 | f         | f (1 row) 

Let's look at the data structure the SELECT statement is accessing:

 phpbook=# \d seq_showseq  Sequence "seq_showseq"     Column     |  Type ---------------+---------  sequence_name | name  last_value    | bigint  increment_by  | bigint  max_value     | bigint  min_value     | bigint  cache_value   | bigint  log_cnt       | bigint  is_cycled     | boolean  is_called     | boolean 

As you can see, PostgreSQL has to store quite a lot of information about the sequence.

To select the next value in the sequence, use the function called nextval:

 phpbook=# SELECT nextval('seq_showseq');  nextval ---------        1 (1 row) 

The first value in the sequence is 1. If the sequence is accessed again, the value will be two:

 phpbook=# SELECT nextval('seq_showseq');  nextval ---------        2 (1 row) 

In some cases it is necessary to modify this value. Especially when something has gone wrong in your application, it might be necessary to change to the next value. This can easily be done with the help of the setval function:

 phpbook=# SELECT setval('seq_showseq', 400);  setval --------     400 (1 row) 

The value of the sequence has been set to 400. If the sequence is accessed the next time, the value will be 401:

 phpbook=# SELECT nextval('seq_showseq');  nextval ---------      401 (1 row) 

As you have seen in the first example of this section, the sequence can be accessed just like a table. But is it possible to work with UPDATE instead of setval?

 phpbook=# UPDATE seq_showseq SET last_value=999; ERROR:  You can't change sequence relation seq_showseq 

PostgreSQL does not accept the UPDATE command when working with sequences, so setval has to be used.