Modifying Databases


After a data structure has been created, you may have to change it because of new demands or things you have not considered when developing the structure of the tables. In this case, modifications have to be performed. This section shows how that can be done.

A General Overview

Building advanced and modular data structures does not always completely protect you from modifying your data structure.

Renaming tables, dropping columns , or changing data types has always been something unpleasant when working with relational databases. Many databases support only fundamental features for changing existing databases or data structures.

This section is dedicated to all database administrators and developers who have to modify their PostgreSQL database. We will try to cover all crucial things concerning operations, such as changing the name of a table or modifying constraints.

Using ALTER

ALTER is an essential command when modifying objects, such as tables and users. In this section, you will learn to use ALTER efficiently .

A Short Overview

ALTER TABLE is an extremely useful command. We cannot only use it to add columns to a table (as we have seen), we can also use it to perform important changes.

Before we get to some real-world examples where we will use the ALTER TABLE command, let's take a look at the syntax overview:

 Command:     ALTER TABLE Description: Modifies table properties Syntax: ALTER TABLE table [ * ]     ADD [ COLUMN ] column type ALTER TABLE table [ * ]     ALTER [ COLUMN ] column {  SET DEFAULT value  DROP DEFAULT } ALTER TABLE table [ * ]     RENAME [ COLUMN ] column TO newcolumn ALTER TABLE table     RENAME TO newtable ALTER TABLE table     ADD table constraint definition 

Now we'll present some examples that show you how you can effectively use the ALTER TABLE command.

Changing Names

In the previous section, we created a table called hackers , and we now want to rename it programmers .

  ALTER TABLE hackers RENAME TO programmers;  

If we have the permission to change the name, we have now successfully changed the name of the table:

 persons=#  \d programmers  Table "programmers"  Attribute     Type      Modifier -----------+-------------+----------  name       varchar(50)   location   varchar(50) 

Changing the name of a table is sometimes not enough. We will now rename a column in programmers table:

  ALTER TABLE programmers RENAME location TO project;  

Changing the name of a column is as easy as changing the name of a table. You can see in the example that the only thing we have to define is the name of the table we want to change, the old name of the column, and the new name for the column.

These changes are very easy, but how do these changes create an effect on tables or views related to that column?

How Changes Affect Objects Related to other Objects

In the following example, we will create a view that accesses the table we want to change and we will see what happens:

  CREATE VIEW withname_view AS   SELECT 'name: '  name, project FROM programmers;  

The view we have just defined does nothing more than add name: to the first column:

 persons=#  SELECT * FROM withname_view ;  ?column?          project --------------------------+---------  name: Donald Becker        name: Alexandre Julliard  (2 rows) 

The view is defined as:

 persons=#  \d withname_view  View "withname_view"  Attribute     Type      Modifier -----------+-------------+----------  ?column?   varchar()     project    varchar(50)  View definition: SELECT ('name: '::"varchar"  programmers.name), programmers.project FROM programmers; 

But what happens when we change the name of a column the view accesses? Let's perform the change:

 persons=#  ALTER TABLE programmers RENAME project TO softwareproject;  ALTER persons=# SELECT * FROM withname_view;          ?column?          project --------------------------+---------  name: Donald Becker        name: Alexandre Julliard  (2 rows) 

We can see that the view still returns the right result for the query.

 persons=#  \d withname_view  View "withname_view"  Attribute     Type      Modifier -----------+-------------+----------  ?column?   varchar()     project    varchar(50)  View definition: SELECT ('name: '::"varchar"  programmers.name), programmers.softwareproject AS project FROM programmers; 

When looking at the definition of the view, we recognize that project has been changed to softwareproject automatically. This is indeed very comfortable and takes away a lot of troubles when working with PostgreSQL. Imagine a situation where you have to deal with dozens of tables or views that are all related to each other. It would be a nightmare to have to make all changes in all those views manually. PostgreSQL does the job for you.

Dropping Columns

Sometimes, sweeping your database might be a very useful task. The "cut off your toe" approach to database alteration can bring back some simplicity and clarity to your data structure and save a lot of disk space. But how can we remove columns from a table?

As with most other ANSI SQL supporting databases, PostgreSQL offers no command to remove columns from a table. In a way, this is not very convenient for the user , but it also has some important advantages. Databases are mostly used in combination with complex applications. Imagine a database administrator who recognizes that a column is no longer needed. If he simply drops the column, what happens to the applications that works on the database? Some SQL statements using that column will not work anymore. This error may be very obvious, but the following example written in Perl will show you a problem that is much more difficult to solve. Things can be a little tricky, especially when such things happen in complex applications that are not programmed properly. We have included this example here to make clear that a DROP COLUMN command can be a dangerous feature:

 $getabhol="SELECT * FROM house WHERE prodid='1234' "; $stha=$dbhlocal->prepare("$getabhol") or         &makelog("cannot proceed\n",1); $stha->execute() or         &makelog("cannot proceed\n",1); @prod = $stha->fetchrow_array; $id=@prod[0]; $prodname=@prod[1]; $prodcat=@prod[2]; 

The example has been taken from a real-world system; we have just added some nasty "features" to show you which hazards you might have to face.

In the first line, we compile an SQL command that is executed. Then the first line of the result is assigned to the array called @prod . The first field of the array @prod[0] is assigned to $id . The next two fields of the first line of the result are also assigned to variables . Assume that the house table has ten columns of data type text. We use the first three columns of those ten columns and assign them to variables. Now imagine that the second column of the house table is removed by the database administrator. The previous code would still work without any trouble. The only problem is that $prodname and $prodcat will get the wrong values. The problem is indeed very obvious as long as the code is very small, but in complex applications, a wrong result could appear anywhere and in any output the application produces. If the error is not discovered immediately, this can lead to enormous problems in production environments. The main problem in the previous code is that we use SELECT * and not something like SELECT id, prodname, prodcat (this would lead to an error as soon as prodname is not available anymore) instead.

In most cases, adding columns will not lead to problems; removing columns can indeed be very dangerous for your system, as you can see in the previous example.

In the next example, we will show a method that can be used as a workaround for the missing DROP COLUMN command. You can use it if you want to drop columns, if you know exactly what you are doing, and if you don't damage an application that uses your database. This workaround is a little more difficult than a DROP COLUMN command.

Assume that we want to drop the column called softwareproject from the following table.

 persons=#  \d programmers  Table "programmers"     Attribute        Type      Modifier -----------------+-------------+----------  name             varchar(50)   softwareproject  varchar(50) 

We need the following commands:

  CREATE TABLE newtable AS SELECT name FROM programmers;   DROP TABLE programmers;   ALTER TABLE newtable RENAME TO programmers;  

Let's see whether all data has successfully been inserted into the new table:

 persons=#  SELECT * FROM programmers;  name --------------------  Donald Becker  Alexandre Julliard (2 rows) 

The table has only one column now because we haven't included the second column of the previous table into the AS SELECT statement. In this sample code, you can see that we have created a new table with all the columns we need in the new table first. This is done with a CREATE TABLE command. Then we drop the old table and rename the temporary table to the name of the old column.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net