3.25. Tables

3.25.1. Creating Tables Using CREATE TABLE

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.

3.25.2. Defining Columns

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).

3.25.2.1. Column Constraints

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.

Note

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 [TAB], instead of [ENTER], the focus proceeds to the Column Constraint options, described below. Once one is selected (and required data filled, if appropriate), the focus proceeds to the Add Column button. Once the column is added, the focus returns to the Column Name entry box. In this way it is possible to create a simple table definition without using the mouse.

3.25.3. Table Constraints

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.

3.25.4. Options

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).

3.25.5. Privileges

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:

1. 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).

2. Specifying the set of privileges with the check buttons located at the right.

3. 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:

3.25.6. Creating Tables Using CREATE TABLE AS

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.

1. Expand the web_store database (click the [O-] beside the database name), then expand web_store_schema.

2. 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.

3. In the Name For The New Table field, type: item_availability

4. 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.

5. 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.

6. 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;

   

   Figure 3-80. The CREATE TABLE AS Dialog

7. 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.