PostgreSQL Modify Table Structure

5.5. Modify the table
If you create a table and find yourself making a mistake, or if your application needs have changed, you can delete the table and recreate it. But if the table is already populated with a lot of data, or if the table is already referenced by other database objects (such as a foreign KEY constraint) then this is not a convenient option. PostgreSQL therefore provides a family of commands to modify an existing table. Note that it is conceptually different from modifying the data contained in a table: Here we are interested in modifying a table definition, or structure.

You can add fields, delete fields, add constraints, delete constraints, modify default values, modify field data types, rename fields, rename tables.

All of these actions are performed with the ALTER TABLE command.

5.5.1. Adding Fields
To add a field, use a command such as the following:

ALTER TABLE Products ADD COLUMN description text; The new field is initially populated with the given default values for rows that already exist in the table (the default is null if you do not declare the default clause).

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

ALTER TABLE Products ADD COLUMN Description text CHECK (description <> "); In fact, all the options described in CREATE TABLE that can be applied to a field can be used here. However, it is important to note that the default value must satisfy the given constraint, otherwise the add will fail. In addition, you can add constraints after you have correctly populated the values of the new fields (see below).

5.5.2. Deleting a field
To delete a field, use the following command:

ALTER TABLE Products DROP COLUMN description; No matter what data is in the field, it will be hours. The constraints associated with this field are also deleted. However, if this field is referenced by a foreign key in another table, PostgreSQL does not implicitly delete the constraint. You can authorize the deletion of anything that depends on the field by using CASCADE:

ALTER TABLE Products DROP COLUMN description CASCADE; See section 5.11 For information about the mechanisms behind these operations.

5.5.3. Adding Constraints
To add a constraint, use the table constraint syntax. Like what:

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 non-null constraint that cannot be written as a table constraint, use the following syntax:

Alter TABLE products ALTER COLUMN product_no SET is not NULL;
This constraint is checked immediately, so the table must conform to the constraint before adding the constraint.

5.5.4. Deleting a constraint
To delete a constraint, you need to know its name. If you give it a name, it's a good thing. Otherwise the system assigns a generated name, so you need to find it. Psql command/d tablename here to help; other interfaces may also provide a way to check the details of the table. And then this is the command:

ALTER TABLE Products DROP CONSTRAINT some_name; (If you're working with a generated constraint name, like $ $, don't forget that you need to add double quotes to it to make it a valid identifier.) ）

As with the Delete field, if you want to delete a dependency constraint, you need to use CASCADE. An example is a FOREIGN key constraint that relies on a unique constraint or a PRIMARY KEY constraint on the referenced field.

All constraint types are used in addition to non-null constraints. To delete a non-empty type, use the

alter TABLE products ALTER COLUMN product_no DROP is not NULL; (Remember that the non-null constraint has no name.) ）

5.5.5. changing the default value of a field
To set a default value for a field, use a command such as the following:

ALTER TABLE products ALTER COLUMN price SET default 7.77; Note that doing so does not affect existing rows of data in any table, it simply changes the default values for future INSERT commands.

To delete the default value, use the

alter TABLE products alter COLUMN price DROP DEFAULT; This is actually equivalent to setting the default to NULL. As a result, if we delete a default value that has not yet been defined, it is not an error, because the default implication is a null value.

5.5.6. modifying the data type of a field
To convert a field to another data type, use the following command:

alter TABLE products ALTER COLUMN price TYPE numeric (10,2); It is possible to succeed only if each of the existing items in the field can be converted to a new type of city with an implied type. If you need a more complex conversion, you can add a USING clause that declares how to calculate the new value from the old value.

PostgreSQL will attempt to convert the default value of the field (if it exists) to a new type, as well as any constraints that involve that field. However, these conversions may fail, or may produce strange results. Before modifying a field type, you might want to remove those constraints and then add them manually.

5.5.7. changing a name to a field
Rename a field:

ALTER TABLE Products RENAME COLUMN product_no to Product_number;
5.5.8. Changing a name to a field
To rename a field:

ALTER TABLE Products RENAME COLUMN product_no to Product_number;
5.5.9. changing a name to a table
To rename a table:

ALTER TABLE products RENAME to items;

Excerpt from: http://blog.csdn.net/joulegates/article/details/1534699

PostgreSQL Modify Table Structure