Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :
ALTER TABLE -- change the definition of a table
ALTER TABLE name action [, ...] ALTER TABLE name RENAME COLUMN column TO new_column ALTER TABLE name RENAME TO new_name
where action is one of:
ADD column type [ column_constraint [ ... ] ] DROP COLUMN column ADD table_constraint DROP CONSTRAINT constraint_name [ CASCADE ]
ALTER TABLE changes the definition of an existing table. There are several subforms:
ADD column type
This form adds a new column to the table using the same syntax as CREATE TABLE.
This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.
This form adds a new constraint to a table using the same syntax as CREATE TABLE.
This form drops constraints on a table. Currently, constraints on tables are not required to have unique names, so there may be more than one constraint matching the specified name. All matching constraints will be dropped.
The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data.
You must own the table to use ALTER TABLE.
The name (possibly schema-qualified) of an existing table to alter.
Name of a new or existing column.
New name for an existing column.
New name for the table.
Data type of the new column.
New table constraint for the table.
Name of an existing constraint to drop.
Automatically drop objects that depend on the dropped constraint.
When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column’s default value (null if no DEFAULT clause is specified).
Adding a column with a non-null default will require the entire table to be rewritten. This may take a significant amount of time for a large table; and it will temporarily require double the disk space.
Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
Changing any part of a system catalog table is not permitted. Refer to CREATE TABLE for a further description of valid parameters.
To add a column of type VARCHAR2 to a table:
ALTER TABLE emp ADD address VARCHAR2(30);
To drop a column from a table:
ALTER TABLE emp DROP COLUMN address;
To rename an existing column:
ALTER TABLE emp RENAME COLUMN address TO city;
To rename an existing table:
ALTER TABLE emp RENAME TO employee;
To add a check constraint to a table:
ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal > 500);
To remove a check constraint from a table: