ALTER TABLE {ONLY table
| table *
| table} ADD [ COLUMN ] column type |
ALTER TABLE {ONLY table
| 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 |
ALTER TABLE table OWNER TO newowner |
ALTER TABLE table CREATE TOAST TABLE |
The name of an existing table that you want to alter:
ONLY table changes apply only to the current table
table * changes apply to the current table and its children
table changes apply depending on the inheritance mode.
Name of a new or existing column.
Type of the new column.
New name for an existing column.
New name for the table.
New table constraint for the table
The user name of the new owner of the table.
Message returned from column or table renaming.
Message returned if table or column is not available or if some other error reason is encountered.
Message returned if toast table already exists.
Message returned if a toast table is not required.
ALTER TABLE changes the definition of an existing table.
The ADD COLUMN form adds a new column to the table and uses the same syntax as CREATE TABLE.
The ALTER COLUMN form allows you to set or remove the default for the column. Note that defaults only apply to newly inserted rows.
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 the same type and size after this command is executed.
The ADD table constraint definition clause adds a new constraint to the table using the same syntax as CREATE TABLE.
The OWNER clause changes the owner of the table to the user newowner.
The CREATE TOAST TABLE clause requests that a comparison table and associated B-tree index be created to store large column values.
You must own the table in order to change its schema.
The keyword COLUMN is superfluous and can be omitted.
In the current implementation, default and constraint clauses for the new column will be ignored. You can use the SET DEFAULT form of ALTER TABLE to set the default later. (You will also have to update the already existing rows to the new default value, using UPDATE.)
In the current implementation, only FOREIGN KEY constraints can be added to a table. To create or remove a unique constraint, create a unique index (see CREATE INDEX). To add check constraints you need to recreate and reload the table, using other parameters to the CREATE TABLE command.
You must own the table in order to change it. You cannot rename any part of the schema of a system catalog. Refer to the Section called Inheritance in Chapter 1 for more information on inheritance.
Refer to CREATE TABLE for a further description of valid arguments.
A TOAST table will only be created if the table has:
At least one column with a variable length and unknown maximum size, or,
At least one variable length column and the tuple total length (considering the maximum sizes of the variable length columns and all overhead) is greater than TOAST_TUPLE_THRESHOLD.
To create a table:
CREATE TABLE tmp (initial int4); |
To add a column of type int4 to a table:
ALTER TABLE tmp ADD COLUMN a int4; |
To rename an existing column:
ALTER TABLE tmp RENAME COLUMN a TO aa; |
To rename an existing table:
ALTER TABLE tmp RENAME TO tmpa; |
To add a foreign key constraint to a table:
CREATE TABLE tmpx (a int4 primary key); ALTER TABLE tmpa ADD CONSTRAINT tmpconstr foreign key(b) reference tmpx(a) MATCH FULL; |
The ADD COLUMN form is compliant with the exception that it does not support defaults and constraints, as explained above. The ALTER COLUMN form is in full compliance.
SQL92 specifies some additional capabilities for ALTER TABLE statement which are not yet directly supported by PostgreSQL:
ALTER TABLE table DROP CONSTRAINT constraint { RESTRICT | CASCADE }
|
Removes a table constraint (such as a check constraint, unique constraint, or foreign key constraint). To remove a unique constraint, drop a unique index. To remove other kinds of constraints you need to recreate and reload the table, using other parameters to the CREATE TABLE command.
For example, to drop any constraints on a table distributors:
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 }
|
Removes a column from a table. Currently, you must recreate and reload the table in order to remove an existing column:
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;
|
The clauses to rename columns and tables are PostgreSQL extensions from SQL92.