Use the Table dialog to create or modify a table.
The Table dialog organizes the development of a table through the following dialog tabs: General, Columns, Constraints, Advanced, Parameter, and Security. The SQL tab displays the SQL code generated by dialog selections.
Use the fields in the General tab to identify the table:
Click the Columns tab to continue.
Use the drop-down listbox next to Inherited from table(s) to specify any parent table(s); the table will inherit columns from the selected parent table(s). Click inside the Inherited from table(s) field to select a table name from a drop-down list. Repeat to add any other parent tables. Delete a selected table by clicking the x to the left of the parent name. Note that inherited column names and datatypes are not editable in the current dialog; they must be modified at the parent level.
Click the Add icon (+) to specify the names of columns and their datatypes in the Columns table:
Click the Add icon (+) to add additional columns; to discard a column, click the trash icon to the left of the row and confirm deletion in the Delete Row popup.
Click the Constraints tab to continue.
Use the fields in the Constraints tab to provide a table or column constraint. Optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an INSERT or UPDATE operation to succeed. Select the appropriate constraint type by selecting one of the following tabs on the Constraints panel:
Tab Name | Constraint |
---|---|
Primary Key | Provides a unique identifier for each row in the table. |
Foreign Key | Maintains referential integrity between two tables. |
Check | Requires data satisfies an expression or condition before insertion or modification. |
Unique | Ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table. |
Exclude | Guarantees that if any two rows are compared on the specified column or expression (using the specified operator), at least one of the operator comparisons will return false or null. |
To add a primary key for the table, select the Primary Key tab, and click the Add icon (+). To define the primary key, click the Edit icon to the left of the Trash icon. A dialog similar to the Primary key dialog (accessed by right clicking on Constraints in the pgAdmin tree control) opens.
Use the fields in the General tab to identify the primary key:
Click the Definition tab to continue.
Use the fields in the Definition tab to define the primary key constraint:
To add a foreign key constraint, select the Foreign Key tab, and click the Add icon (+). To define the constraint, click the Edit icon to the left of the Trash icon. A dialog similar to the Foreign key dialog (accessed by right clicking on Constraints in the pgAdmin tree control) opens.
Use the fields in the General tab to identify the foreign key constraint:
Click the Definition tab to continue.
Use the fields in the Definition tab to define the foreign key constraint:
Move the Deferrable? switch to the Yes position to specify the timing of the constraint is deferrable and can be postponed until the end of the statement. The default is No.
If enabled, move the Deferred? switch to the Yes position to specify the timing of the constraint is deferred to the end of the statement. The default is No.
Move the Match type switch specify the type of matching that is enforced by the constraint:
- Select Full to indicate that all columns of a multicolumn foreign key must be null if any column is null; if all columns are null, the row is not required to have a match in the referenced table.
- Select Simple to specify that a single foreign key column may be null; if any column is null, the row is not required to have a match in the referenced table.
Move the Validated switch to the Yes position to instruct the server to validate the existing table content (against a foreign key or check constraint) when you save modifications to this dialog.
Move the Auto FK Index switch to the No position to disable the automatic index feature.
The field next to Covering Index generates the name of an index if the Auto FK Index switch is in the Yes position; or, this field is disabled.
Click the Columns tab to continue.
Use the fields in the Columns tab to specify one or more reference column(s). A Foreign Key constraint requires that one or more columns of a table must only contain values that match values in the referenced column(s) of a row of a referenced table:
Click the Add icon (+) to add a column to the list; repeat the steps above and click the Add icon (+) to add additional columns. To discard an entry, click the trash icon to the left of the entry and confirm deletion in the Delete Row popup.
Click the Action tab to continue.
Use the drop-down listboxes on the Action tab to specify behavior related to the foreign key constraint that will be performed when data within the table is updated or deleted:
The supported actions are:
NO ACTION | Produce an error indicating that the deletion or update will create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if any referencing rows still exist. This is the default. |
RESTRICT | Throw an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. |
CASCADE | Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively. |
SET NULL | Set the referencing column(s) to null. |
SET DEFAULT | Set the referencing column(s) to their default values. There must be a row in the referenced table that matches the default values (if they are not null), or the operation will fail. |
To add a check constraint, select the Check tab on the panel, and click the Add icon (+). To define the check constraint, click the Edit icon to the left of the Trash icon. A dialog similar to the Check dialog (accessed by right clicking on Constraints in the pgAdmin tree control) opens.
Use the fields in the General tab to identify the check constraint:
Click the Definition tab to continue.
Use the fields in the Definition tab to define the check constraint:
To add a unique constraint, select the Unique tab on the panel, and click the Add icon (+). To define the constraint, click the Edit icon to the left of the Trash icon. A dialog similar to the Unique constraint dialog (accessed by right clicking on Constraints in the pgAdmin tree control) opens.
Use the fields in the General tab to identify the unique constraint:
Click the Definition tab to continue.
Use the fields in the Definition tab to define the unique constraint:
To add an exclusion constraint, select the Exclude tab on the panel, and click the Add icon (+). To define the constraint, click the Edit icon to the left of the Trash icon. A dialog similar to the Exclusion constraint dialog (accessed by right clicking on Constraints in the pgAdmin tree control) opens.
Use the fields in the General tab to identify the exclusion constraint:
Click the Definition tab to continue.
Use the fields in the Definition tab to define the exclusion constraint:
Use the drop-down listbox next to Tablespace to select the tablespace in which the index associated with the exclude constraint will reside.
Use the drop-down listbox next to Access method to specify the type of index that will be used when implementing the exclusion constraint:
- Select gist to specify a GiST index (the default).
- Select spgist to specify a space-partitioned GiST index.
- Select btree to specify a B-tree index.
- Select hash to specify a hash index.
Use the Fill Factor field to specify a fill factor for the table and associated index. The fill factor is a percentage between 10 and 100. 100 (complete packing) is the default.
Move the Deferrable? switch to the Yes position to specify that the timing of the constraint is deferrable, and can be postponed until the end of the statement. The default is No.
If enabled, move the Deferred? switch to the Yes position to specify the timing of the constraint is deferred to the end of the statement. The default is No.
Use the Constraint field to provide a condition that a row must satisfy to be included in the table.
Click the Columns tab to continue.
Use the fields in the Columns tab to to specify the column(s) to which the constraint applies. Use the drop-down listbox next to Column to select a column and click the Add icon (+) to provide details of the action on the column:
Click the Advanced tab to continue.
Use the fields in the Advanced tab to define advanced features for the table:
Use the fields in the Like box to specify which attributes of an existing table from which a table will automatically copy column names, data types, and not-null constraints; after saving the new or modified table, any changes to the original table will not be applied to the new table.
Click the Parameter tab to continue.
Use the tabs nested inside the Parameter tab to specify VACUUM and ANALYZE thresholds; use the Table tab and the Toast Table tab to customize values for the table and the associated toast table:
Provide a custom value in the Value column for each metric listed in the Label column.
Click the Security tab to continue.
Use the Security tab to assign privileges and define security labels.
Use the Privileges panel to assign privileges to a role. Click the Add icon (+) to set privileges for database objects:
Click the Add icon (+) to assign additional privileges; to discard a privilege, click the trash icon to the left of the row and confirm deletion in the Delete Row popup.
Use the Security Labels panel to define security labels applied to the function. Click the Add icon (+) to add each security label selection:
Click the Add icon (+) to assign additional security labels; to discard a security label, click the trash icon to the left of the row and confirm deletion in the Delete Row popup.
Click the SQL tab to continue.
Your entries in the Table dialog generate a SQL command (see an example below). Use the SQL tab for review; revisit or switch tabs to make any changes to the SQL command.
Example
The following is an example of the sql command generated by user selections in the Table dialog:
The example shown demonstrates creating a table named product_category. It has three columns and a primary key constraint on the category_id column.