In the object hierarchy under every schema are Tables. You can add tables by selecting CREATE TABLE or CREATE TABLE AS from the Tables context menu. Once a table has been created, it can be altered, analyzed, backed up, clustered, commented on, dropped, renamed, reindexed, viewed, and vacuumed. This section describes how to create, manage, and drop a table with Administrator.
A table is an object under a schema, so to create a table, first expand the database under which you wish to create the table (click the [O-] beside the database name) and then expand web_store_schema, under which the table is to be created.
To create a table, right-click on Tables and choose CREATE TABLE. The CREATE TABLE dialog box appears.
In the Name For The New Table field, type the name for the new table. Once this is done, you must enter the table definition.
There are four tabs in the create table dialog. The first tab, Columns, allows the definition of table columns, which optionally may have column constraints. The second tab, Table Constraints, permits you to define table constraints. Options such as inheritance can be specified using the Options tab. Finally, you can use the Privileges tab to define which users should have privileges to use the newly created table. This is similar to what could have been done with a SQL92 CREATE SCHEMA command.
Once the table definition has been entered, options chosen, and privileges selected, click OK to create the table and close the dialog window. If the backend cannot create the table due to some error (such as the table name already exists), an error message appears. After you dismiss the error message window, the CREATE TABLE dialog remains available so that you can make corrections and retry the operation.
The first tab of the CREATE TABLE dialog allows for column definitions. Your table must have at least one column defined unless it inherits from some other table (it should probably still have some columns defined, but that is up to you to decide). The minimum column definition requires a column name and a data type (with perhaps a precision/scale specification). After entering these specifications, click the Add Column button to add that column definition to the columns that will be part of the table once it is created.
The column definition then shows up in the Current Column Definitions list. Once created, the column definition cannot be altered, but it can be removed with the Remove button and recreated. Column definitions are added to the end of the list, but definitions can be moved up or down the list with the Move Up and Move Down buttons (select the column definition and click the appropriate button).
Column constraints are (logically) mutually exclusive, so radio buttons allow you to choose which constraint to use, if any. The exception is that UNIQUE and NOT NULL can both be specified. A special Unique Not Null radio button is provided for this case.
Some constraints, namely DEFAULT, CHECK, and REFERENCES, require further specification. Once selected, the appropriate entry boxes and widgets become active. You must select the appropriate options as you would with plain SQL commands. There is also a box for specifying an optional name for the column constraint. In the case of the Unique Not Null option, the constraint name applies to the NOT NULL constraint, as that can only be defined as a column constraint while UNIQUE can also be defined as a table constraint and thus have a name associated with it.
Once the constraint has been selected (and additional data entered), you can click the Add Column button. The column, with the defined constraint, is added to the Current Column Definitions list.
The current version does not allow for the specification of column (or table) comments while creating the table, so you have to add them afterwards with the COMMENT menu option.
The focus cycles through the column name, data type (and related)
fields, and the Add Column button.
If the focus is on this
button and you press
The second tab of the CREATE TABLE dialog permits the definition of table constraints. As with the creation of columns described above, you specify a table constraint, one at a time, and then add the definition to the Current Table Constraint Definitions list box by selecting the Add Table Constraint button. Once created, constraint definitions cannot be changed, but they can be removed with the Remove button and recreated. Remember that the table has not yet been created—an attempt to create the table will happen only after the OK button in the dialog is clicked. At this point you are only compiling a list of definitions to be used in the creation of the table.
A table constraint is defined by selecting its type with the radio buttons and then filling the required information for that type of constraint. For UNIQUE and PRIMARY KEY constraints, this information consists of a comma-separated list of columns. To select the columns, click the "..." button on the right. The button brings up a dialog box that contains all the defined/inhertied columns that have been defined thus far. The CHECK constraint requires a valid SQL expression. The FOREIGN KEY constraint requires the specification of the foreign key columns (which can be selected in the same way as columns for Unique/Primary Key constraints) and the Referenced table, at least. Optionally you can specify the name of the columns in the referenced table and referential integrity constraint options (refer to the PostgreSQL 7.3 Reference Manual for more details on table constraints).
Table constraints can optionally have names. To name a constraint you should fill the Constraint Name text entry field before pressing the Add Table Constraint button.
The third tab of the CREATE TABLE dialog allows for the specification of table creation options. You can select whether the table should have OIDs associated with each of its rows and select parent tables for inheritance. (Refer to the PostgreSQL 7.3 Reference Manual for details about OIDs and the PostgreSQL inheritance concept.)
The use of OIDs can be specified by selecting the desired radio button option. To specify a table as a parent table, select the table (or tables) in the Available Tables list and use the Add button to move it (or them) to the Parent Tables list. Tables can be removed from the Parent Tables list with the Remove button and reordered with the Move Up and Move Down buttons (select a table and press the appropriate button).
The fourth and last tab of the CREATE TABLE dialog allows you to specify who can use the newly created table and how they can use it.
The Privileges tab also enables you to specify who will own the newly created table, assuming the Administrator user has the right to create tables on behalf of others (that is, this user is a superuser). This selection can be done in the Table Owner combo-box, which is initially filled with the PostgreSQL default owner, the current tool user.
The association between users or groups and privileges is done by:
Selecting all the users and/or groups that will receive the same set of privileges on the Users and Groups list (a special check button is provided for the special grantee PUBLIC).
Specifying the set of privileges with the check buttons located at the right.
Clicking the Add Privileges button.
The process must be repeated for each different set of privilege definitions, or at least to grant some users or groups the additional privileges that were not granted initially.
As with the other tabs in this dialog, the privileges defined are added to the Current Privilege Definitions list. As before, they cannot be edited but can be removed and recreated.
Administrator also enables you to perform the following operations on an existing table:
By default, the user creating the table is the owner. However, the owner can later be modified by a superuser. To modify the owner of a table, right-click on the table and select ALTER TABLE OWNER. A dialog pops up asking for the new ownername. Because the owner name can be only one of the users in the cluster, the dialog has a drop down box from which the user can be selected. Once the username is selected, click OK.
To cluster a table, right-click on it and select CLUSTER TABLE. Complete the dialog that appears and click OK.
PostgreSQL allows commenting on tables, which makes them easier to identify. To comment on a table, right click on it, and select COMMENT. A dialog pops up asking for the comment. If there is already a comment on the table, this comment is displayed. Type the new comment in the edit box and click OK to save the new comment.
To drop a table, right-click on it and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the table if there are any dependent objects. DROP CASCADE automatically drops the table and all objects that depend on it.
If Preferences => Ask For Confirmations is enabled, you are asked to confirm that you want the table dropped (and all the dependent objects in case of a DROP CASCADE); if it is disabled, the table is dropped immediately (along with its dependent objects if DROP CASCADE was selected). This action cannot be undone.
To reindex a table, right-click on it and select REINDEX. Complete the dialog that appears and click OK.
To rename a table, right-click on it and select RENAME TABLE. Type the new name in the dialog that appears and click OK.
In addition to creating a table using the CREATE TABLE dialog, you can also create a table by using the CREATE TABLE AS option. The latter allows you to create a table comprised of columns selected from other tables.
In this example you will create a table named item_availability using CREATE TABLE AS.
Expand the web_store database (click the [O-] beside the database name), then expand web_store_schema.
To create a table using columns from another table, right-click on Tables and choose CREATE TABLE AS. The CREATE TABLE AS dialog box appears.
In the Name For The New Table field, type: item_availability
Next is the Column Names field. You can use this optional field to specify new column names in the table you are creating (in case you do not want to use the same column names as those used in the source tables). For this example, you need to make the name of the second column quantity_available. To do this, you need to type in: item_id, quantity_available
You specify both names because PostgreSQL has no way of knowing which column will take the new name (when more columns than given new names exist), so it assigns the new names starting from the first column in the table.
The next field is the Imported From File. Note that this field is not editable; it displays the path to the selected file, if you have imported the SQL Select Statement from another file (see below for instructions on how to do that). For this example you are not importing a file, so this field remains blank.
The next field is the SQL SELECT Statement. This is the field where you type in the query to select specific columns from different tables to make the new table you are creating. For this example, type:
SELECT items.item_id, inventory.quantity FROM web_store_schema.items, web_store_schema.inventory WHERE items.item_id = inventory.item_id;
Click OK to create the table.
The table now contains up to date information on items and how many of each is available. Note that the information is up to date as of the time of creation. However, if the table needs to be kept up to date after creation, it has to be manually updated as required.
Below the SQL SELECT Statement edit box, are the Clear, Import From File, and Export To File buttons:
Clear clears any statements in the SQL SELECT Statement edit box. Once cleared, the statement cannot be retrieved unless it was imported from a file or was exported to a file before clearing.
Import From File allows you to import a previously saved SQL SELECT Statement from a file.
Export To File allows you to export the text in the SQL SELECT Statement edit box to a file.