Alter table: add, delete, change column names, modify column constraints, and change table names

Alter   Table
Name
Alter   Table -Change table attributes
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
Inputs
Table  
Name of the existing table to be modified.
Column  
Existing or new column names.
Type
The type of the new column.
Newcolumn
New name of the existing column.
Newtable
The new name of the table.
Table   Constraint Definition
The new constraint definition of the table.
New Table   Constraint   For The Table

Output
Alter  
Information returned from the renamed column or table.
Error
Information returned if a column or table does not exist.
Description
Alter   Table Change the definition of an existing table. Add   Column Format and Create Add a new column to the table with the same syntax as the table. / Field. Alter   Column Form allows you / Set or delete the default value in the field ). Note that the default value is only valid for newly inserted rows. The rename clause can change a table or column without affecting any data in the relevant table. / Field name. Therefore, tables or columns / Fields of the same size and type will remain after this command is executed. Add   Table   Constraint Definition clause Create The same table syntax adds a new constraint to the table.

To change the attributes of a table, you must be the table owner.

Note:
Column Keywords are redundant and can be omitted.
If" * "After a table name, this command is used to operate on the table and all tables with lower inheritance levels than the table. If this parameter is left blank, this attribute (change) does not add any sub-tables or modify the names of any sub-tables. This is always the case when you add or modify the attributes of a parent table (: a table with a higher inheritance level. Otherwise, the following queries are performed at the inheritance level.
Select Newcolumn From Superclass *
It will not work, because the sub-table will have one attribute less than the upper-level table.
In the current implementation, the new columns / The default (value) and constraint clause of the field are ignored. You can use Alter   Table Of Set   Default Format: Set the default value ). (You still have to use Update Update existing rows to the default value .)

In the current implementation, onlyForeign KeyA constraint can be added to a table. To create or delete a unique constraint, you can create a unique index (seeCreate Index). To addCheck(Check) constraints. You need to recreate and reload the table. The parameter used isCreate TableOther parameters of the command.
To modify the table structure, you must be the owner of the table. You cannot change any part of the system table structure. The PostgreSQL User Manual contains more information about inheritance.

Usage
Add Varchar Column:
Alter   Table Distributors Add   Column Address Varchar ( 30 );
Rename existing columns:
Alter   Table Distributors rename Column Address To City;
Rename an existing table:
Alter   Table Distributors rename To Suppliers;
Add a foreign key constraint to the table:
Alter   Table Distributors Add   Constraint Distfk Foreign   Key (Address) References Addresses (Address) Match Full

Compatibility
Sql92add Column The format is compatible, except the default (value) and constraints mentioned above. Alter   Column The format is completely compatible.
Sql92 pair Alter   Table Declared some additional features not directly supported by ipvs:
Alter   Table   Table   Drop   Constraint   Constraint { Restrict   |   Cascade }
Add or delete table constraints (such as check constraints, unique constraints, or foreign key constraints ). To create or delete a unique constraint, create or delete a unique index. To modify other types of constraints, you need to re-create and reload the table and use Create   Table Other parameters of the command.
For example, to delete any constraints of the distributors table:
Create   Table   Temp   As   Select   *   From Distributors;
Drop   Table Distributors;
Create   Table Distributors As   Select   *   From   Temp ;
Drop   Table   Temp ;
Alter   Table   Table   Drop   [ Column ]   Column { Restrict   |   Cascade }
To delete an existing column, the table must be re-created and reloaded:
Create   Table   Temp   As   Select Did, City From Distributors;
Drop   Table Distributors;
Create   Table Distributors (
Did Decimal ( 3 ) Default   1 ,
Name Varchar ( 40 ) Not   Null ,
);
Insert   Into Distributors Select   *   From   Temp ;
Drop   Table   Temp ;
Rename a column / The field and table name are PostgreSQL extensions. Sql92 does not provide these.