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.
When you invoke 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.