Designing PostgreSQL Table Structure

NineData offers a visual table structure design solution. You can complete the design of the table structure by clicking the mouse in the SQL Console, without having to manually enter SQL commands.

Prerequisites

• The data source type is PostgreSQL.

• In organization mode, you have the permission for the SQL Console function module.

• In organization mode, you have read and write permissions for the target data source.

  tip

  If you do not have the above permissions, please contact the system administrator of your organization to configure permissions or bind roles for you.

• In the commercial versions (, ), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOpsservice. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

  

Operation Steps

You can design the table structure when creating or editing a table through the SQL Console. This article demonstrates with the example of creating a table.

1. Open the SQL console and go to the target data source.

2. In the database list on the left side of the SQL Console, right-click the target database, Schema, or any data table, and click .

   tip

   The object you right-click is different, the differences are as follows:

   • Database: The table will be created in a Schema named public by default.
   • Schema: The table will be created in this Schema by default.
   • Data table: The table will be created in the Schema where this data table belongs by default.

3. In the tab, you can start designing the table structure in a visual way. The system will automatically generate at the bottom of the page based on your configuration. For details of the structure design page, please select the corresponding tab below and view.

Click to expand the complete visual table structure parameter description

Common

• : Customize the name of the table.
• : Select which database the current table needs to be created under.
• Schema: Select which Schema the current table needs to be created under.
• : Comment on the table.

Column

Set the column.

• : The text box on the far left, enter the name of the column, the length is up to 64 characters. The first row of the current interface is the primary key column by default, and the column name is id by default.
• : The second text box on the left, click this text box to select the data type of the column.
• NOT NULL: Set whether this column allows null values. The first row of the primary key column in the current interface, this option is fixedly selected, that is, null values are not allowed.
• : Set the default value of the current column, that is, the DEFAULT attribute. If no other value is inserted in this column, this default value will be used.
• : Set the comment of the current column, and set the explanation for the column through the comment. The length is up to 1024 characters.
• : That is, Generated Column, its value can be the calculation result of other columns, after selecting, you also need to enter the expression.
• Collate: Specify the COLLATE for the current column.
• : Add other options according to needs.

Index

Set the index.

• : Enter the index name. If there is a primary key in the current table, the first row is the primary key index information by default.
• : Select the type of index to be added.
• : Select the column that needs to add this index, or enter .
• : Add other options according to needs.

Foreign Key

Set foreign key constraints.

• : Enter the name of the foreign key.
• : Select the foreign key column that needs to be associated with the external table.
• : Select which library, Schema, table, and column to associate the current foreign key column with.
• ON UPDATE: Set the restrictions during the UPDATE operation.
• ON DELETE: Set the restrictions during the DELETE operation.

Constraint

Set the constraints.

• : Enter the name of the constraint.
• : Select the type of constraint to be added.
• : Enter constraint expressions.

Partition

If your database file is too large, you can set partitions to improve read performance.

Partitioned table: Check this item, and then enter the partition statement below, you can refer to the content of on the right for input.

Table Options

Set table-level options.

4. Click , and in the pop-up window, click .

   tip

   You can also copy the SQL script and manually execute it in other database clients.