PostgreSQL 8.0.0rc4 Documentation
Prev | Fast Backward | Chapter 5. Data Definition | Fast Forward | Next

# 5.6. Modifying Tables

When you create a table and you realize that you made a mistake, or the requirements of the application changed, then you can drop the table and create it again. But this is not a convenient option if the table is already filled with data, or if the table is referenced by other database objects (for instance a foreign key constraint). Therefore PostgreSQL provides a family of commands to make modifications on existing tables.

You can

- Add columns,

- Remove columns,

- Add constraints,

- Remove constraints,

- Change default values,

- Change column data types,

- Rename columns,

- Rename tables.

All these actions are performed using the ALTER TABLE command.

## 5.6.1. Adding a Column

To add a column, use this command:

    ALTER TABLE products ADD COLUMN description text;

The new column will initially be filled with null values in the existing rows of the table.

You can also define a constraint on the column at the same time, using the usual syntax:

    ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

A new column cannot have a not-null constraint since the column initially has to contain null values. But you can add a not-null constraint later. Also, you cannot define a default value on a new column. According to the SQL standard, this would have to fill the new columns in the existing rows with the default value, which is not implemented yet. But you can adjust the column default later on.

## 5.6.2. Removing a Column

To remove a column, use this command:

    ALTER TABLE products DROP COLUMN description;

## 5.6.3. Adding a Constraint

To add a constraint, the table constraint syntax is used. For example:

    ALTER TABLE products ADD CHECK (name <> '');
    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
    ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

To add a not-null constraint, which cannot be written as a table constraint, use this syntax:

    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.

## 5.6.4. Removing a Constraint

To remove a constraint you need to know its name. If you gave it a name then that's easy. Otherwise the system assigned a generated name, which you need to find out. The psql command `\d `tablename`` can be helpful here; other interfaces might also provide a way to inspect table details. Then the command is:

    ALTER TABLE products DROP CONSTRAINT some_name;

(If you are dealing with a generated constraint name like `$2`, don't forget that you'll need to double-quote it to make it a valid identifier.)

This works the same for all constraint types except not-null constraints. To drop a not null constraint use

    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

(Recall that not-null constraints do not have names.)

## 5.6.5. Changing a Column's Default Value

To set a new default for a column, use a command like this:

    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

To remove any default value, use

    ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is equivalent to setting the default to null, at least in PostgreSQL. As a consequence, it is not an error to drop a default where one hadn't been defined, because the default is implicitly the null value.

## 5.6.6. Changing a Column's Data Type

To convert a column to a different data type, use a command like this:

    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a `USING` clause that specifies how to compute the new values from the old.

PostgreSQL will attempt to convert the column's default value (if any) to the new type, as well as any constraints that involve the column. But these conversions may fail, or may produce surprising results. It's often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.

## 5.6.7. Renaming a Column

To rename a column:

    ALTER TABLE products RENAME COLUMN product_no TO product_number;

## 5.6.8. Renaming a Table

To rename a table:

    ALTER TABLE products RENAME TO items;

Prev | Home | Next
Inheritance | Up | Privileges