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.