3.6. Columns
In the object hierarchy under every table are Columns. At some point, you may need to add more columns to a user table. Rather than recreating the entire table with another column, PostgreSQL allows you to add columns to an existing user table.
Columns can be added by selecting ADD COLUMN from the Columns context menu. This section describes how to add, modify, and drop a column with Administrator.
In this example, you will create a column called item_sale_price and add it to the table items.
A column is an object under a table, so to create a column, first expand the web_store database, then web_store_schema, then the items table (click the [O-] beside the database, schema, and table names).
Right-click on Columns and select ADD COLUMN.
A dialog box asks for the name of the new column and the data type. In the Name For The New Column field, type: item_sale_price
Because this column will represent the cost of an item when it is on sale, select numeric(7,2) for data type. Refer to the PostgreSQL 7.3 User's Guide for detailed information on each data type and how it can be used.
Click OK to add the column to the table.
Note that the column item_sale_price has been added under the Columns node in the Tree View.
Administrator also enables you to perform the following operations on existing columns:
- Commenting
Commenting on columns makes them easier to identify. To comment on a column, right-click on it and select COMMENT. A dialog asks for the comment. If there is already a comment on the column, this comment is displayed. Type the new comment in the edit box and click OK to save the new comment.
- Dropping
Starting in PostgreSQL 7.3, you can drop user-defined columns on a table. To drop a column, right-click on the column and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the column if there are any dependent objects. DROP CASCADE automatically drops the column and all objects that depend on it.
If Preferences => Ask For Confirmations is enabled, you are asked to confirm that you want the column dropped (and all the dependent objects in case of a DROP CASCADE); if it is disabled, the column is dropped immediately (along with its dependent objects if DROP CASCADE was selected). This action cannot be undone.
- Rename
To rename a column, right-click on it and select RENAME COLUMN. A dialog asks for the new name. Type the new name in the edit box and click OK to save the new name.
3.6.1. ALTER COLUMN
Administrator also enables you to perform ALTER operations on existing columns. To alter a column, right-click on its name in the Tree View and select ALTER COLUMN. A dialog appears with four tabs, each of which allows a different operation:
- Default Value
A column can be given a default value that is used when no other value has been assigned to that column field during a table update. To set, change or drop the default value for a column, select the Default Value tab in the ALTER COLUMN dialog.
- Statistics
Statistics can be set for each column on a table. These values set the statistics-gathering target for subsequent ANALYZE operations. To set the statistics value for a column, select the Statistic tab in the ALTER COLUMN dialog. Standard values can be from 0 to 1000, but you can set a value of -1 to cause default statistics values to be used.
- Not Null
You can mark a column to allow Null values or to reject Null values by selecting the Not Null tab in the ALTER COLUMN dialog. Note that in order for you to choose not to allow null values, the table must currently contain no null values in that column.
- Storage
A column can be set to a specific storage mode. This controls whether this column is held inline or in a supplementary table, and whether the data should be compressed or not.
To set the storage of a column, select the Storage tab in the ALTER COLUMN dialog.
PLAIN must be used for fixed-length values such as INTEGER and is inline, uncompressed.
EXTERNAL is for external, uncompressed data.
The use of EXTERNAL makes substring operations on large TEXT columns faster, at the penalty of increased storage space.
EXTENDED is for external, compressed data. EXTENDED is the default for all data types that support it.
MAIN is for inline, compressible data.
For more information on altering columns, refer to the PostgreSQL 7.3 Reference Manual.