5.5. Modifying Tables

5.5.1. Adding a Column
5.5.2. Removing a Column
5.5.3. Adding a Constraint
5.5.4. Removing a Constraint
5.5.5. Changing a Column's Default Value
5.5.6. Changing a Column's Data Type
5.5.7. Renaming a Column
5.5.8. Renaming a Table
5.5.9. Adding a Node to Distribution Target
5.5.10. Removing a Node from Distribution Target
5.5.11. Changing Distribution Strategy

When you create a table and you realize that you made a mistake, or the requirements of the application change, 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 to existing tables. Note that this is conceptually distinct from altering the data contained in the table: here we are interested in altering the definition, or structure, of the table.

You can:

All these actions are performed using the ALTER TABLE command, whose reference page contains details beyond those given here.

In addition to these actions, in Postgres-XL you can also use ALTER TABLE command to:

In Postgres-XL the following are not allowed:

5.5.1. Adding a Column

To add a column, use a command like:

ALTER TABLE products ADD COLUMN description text;

The new column is initially filled with whatever default value is given (null if you don't specify a DEFAULT clause).

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

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

In fact all the options that can be applied to a column description in CREATE TABLE can be used here. Keep in mind however that the default value must satisfy the given constraints, or the ADD will fail. Alternatively, you can add constraints later (see below) after you've filled in the new column correctly.

Tip

Adding a column with a default requires updating each row of the table (to store the new column value). However, if no default is specified, PostgreSQL is able to avoid the physical update. So if you intend to fill the column with mostly nondefault values, it's best to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.

5.5.2. Removing a Column

To remove a column, use a command like:

ALTER TABLE products DROP COLUMN description;

Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint. You can authorize dropping everything that depends on the column by adding CASCADE:

ALTER TABLE products DROP COLUMN description CASCADE;

See Section 5.13 for a description of the general mechanism behind this.

5.5.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.

Please remember that the distribution column has to be included in UNIQUE and REFERENCE constraints.

5.5.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.)

As with dropping a column, you need to add CASCADE if you want to drop a constraint that something else depends on. An example is that a foreign key constraint depends on a unique or primary key constraint on the referenced column(s).

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.5.5. Changing a Column's Default Value

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

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Note that this doesn't affect any existing rows in the table, it just changes the default for future INSERT commands.

To remove any default value, use:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is effectively the same as setting the default to null. 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.5.6. Changing a Column's Data Type

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

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 might fail, or might 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.5.7. Renaming a Column

To rename a column:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.5.8. Renaming a Table

To rename a table:

ALTER TABLE products RENAME TO items;

5.5.9. Adding a Node to Distribution Target

To add a new node to existing distribution:

ALTER TABLE products ADD NODE (datanode_3);

The datanode must be a valid datanode name. If its a new datanode, then it must have been added to the cluster by appropriate mechanism.

This command will redistribute the existing table data so as to include the new node. For example, in case of DISTRIBUTE BY HASH strategy, hash value for each row must be recomputed and the row must be moved to the selected node based on the new count of datanodes in the distribution list. This operation requires an exclusive lock on the table and hence all read and write access to the table is temporarily blocked. This can be important especially if the table is large and requires considerable time for redistribution.

5.5.10. Removing a Node from Distribution Target

To remove a node from existing distribution:

ALTER TABLE products DELETE NODE (datanode_3);

It must be noted that if you want to remove a node from the cluster, its important that you first remove such node from all existing tables using that node and then only remove the node from the cluster.

Like the previous command, this also takes an exclusive lock on the table, blocking all read and write access to the table.

5.5.11. Changing Distribution Strategy

To change distribution strategy for a table:

ALTER TABLE products DISSTRIBUTE BY REPLICATION;

You can change distribution strategy for a table with this command. For example, a table which was previously distributed by HASH can now be distributed by REPLICATION.

Like the previous command, this also takes an exclusive lock on the table, blocking all read and write access to the table.