GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 6.2(Zoot) |
|
 |
alter_table(l) |
 |
ALTER TABLE − Modifies table properties
ALTER TABLE
table
[ * ] ADD [ COLUMN ] ER">coBLE>
type
ALTER TABLE table
[ * ] RENAME [ COLUMN ] ER">coBLE> TO
newcolumn
ALTER TABLE table
RENAME TO newtable
INPUTS
|
table |
The name of an existing table to alter. |
|||
|
column |
Name of a new or existing column. |
|||
|
type |
Type of the new column. |
newcolumn
New name for an existing column.
newtable
New name for an existing column.
OUTPUTS
|
ALTER |
Message returned from column or table renaming. | ||
|
NEW |
Message returned from column addition. | ||
|
ERROR |
Message returned if table or column is not available. |
ALTER TABLE changes the definition of an existing table. The new columns and their types are specified in the same style and with the the same restrictions as in CREATE TABLE. The RENAME clause causes the name of a table or column to change without changing any of the data contained in the affected table. Thus, the table or column will remain of the same type and size after this command is executed.
You must own the table in order to change its schema.
NOTES
The keyword COLUMN is noise and can be omitted.
’’*’’ following a name of a table indicates that the statement should be run over that table and all tables below it in the inheritance hierarchy; by default, the attribute will not be added to or renamed in any of the subclasses. This should always be done when adding or modifying an attribute in a superclass. If it is not, queries on the inheritance hierarchy such as
SELECT NewColumn FROM SuperClass*
will not work because the subclasses will be missing an attribute found in the superclass.
For efficiency reasons, default values for added attributes are not placed in existing instances of a class. That is, existing instances will have NULL values in the new attributes. If non-NULL values are desired, a subsequent UPDATE query (update(l)) should be run.
You must own the class in order to change its schema. Renaming any part of the schema of a system catalog is not permitted. The PostgreSQL User’s Guide has further information on inheritance.
Refer to CREATE TABLE for a further description of valid arguments.
To add a column of type VARCHAR to a table:
ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
To rename an existing column:
ALTER TABLE distributors RENAME COLUMN address TO city;
To rename an existing table:
ALTER TABLE distributors RENAME TO suppliers;
SQL92
ALTER TABLE/RENAME is a Postgres language extension.
SQL92 specifies some additional capabilities for ALTER TABLE statement which are not yet directly supported by Postgres:
|
.sp |
Puts the default value or constraint specified into the definition of column in the table. See CREATE TABLE for the syntax of the default and table-constraint clauses. If a default clause already exists, it will be replaced by the new definition. If any constraints on this column already exist, they will be retained using a boolean AND with the new constraint. |
Currently, to set new default constraints on an existing column the table must be recreated and reloaded:
CREATE TABLE
temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT 1,
name VARCHAR(40) NOT NULL,
city VARCHAR(30)
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;
|
.sp |
Removes the default value specified by default or the rule specified by constraint from the definition of a table. If RESTRICT is specified only a constraint with no dependent constraints can be destroyed. If CASCADE is specified, Any constraints that are dependent on this constraint are also dropped. |
Currently, to remove a default value or constraints on an existing column the table must be recreated and reloaded:
CREATE TABLE
temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;
|
.sp |
Removes a column from a table. If RESTRICT is specified only a column with no dependent objects can be destroyed. If CASCADE is specified, all objects that are dependent on this column are also dropped. |
Currently, to remove an existing column the table must be recreated 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;
|
alter_table(l) | |