Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Overview

Updated on 2023-03-17 GMT+08:00

This section describes how to create a common table.

A table is a logical structure maintained by a database administrator and consists of rows and columns. You can define a table as a part of your data definitions from the data perspective. Before defining a table, you need to define a database and a schema. This section describes how to use Data Studio to create a table. To define a table in the database, perform the following steps:

  1. In the Object Browser pane, right-click Regular Tables, and choose Create Regular Table.
  2. Define basic table information, such as the table name and table type. For details, see Providing Basic Information.
  3. Define column information, such as the column name, data type schema, data type, and column constraint. For details, see Defining a Column.
  4. For details about how to determine table data distribution settings, see Selecting Data Distribution.
  5. Define column constraints for different constraint types. Constraint types include PRIMARY KEY, UNIQUE, and CHECK. For details, see Defining Table Constraints.
  6. Define table index information, such as the index name and access mode. For details, see Defining an Index.

    On the SQL Preview tab, you can check the automatically generated SQL query. For details, see SQL Preview.

Providing Basic Information

If you create a table in a schema, the current schema will be used as the schema of the table. Perform the following steps to create a common table:

  1. Enter a table name. It specifies the name of the table to be created.

    NOTE:

    Select the Case check box to retain the capitalization of the value of the Table Name parameter. For example, if you enter the table name Employee, the table name will be created as Employee.

    The name of the table schema is displayed in Schema.

  2. Select a table storage mode from the Table Orientation drop-down list.
  3. Select a table type. Its value can be:

    • Normal: a normal table
    • Unlogged: An unlogged table. When data is written to an unlogged table, the data is not recorded in logs. The speed of writing data to an unlogged table is much higher than that of writing data to a common table. However, an unlogged table is insecure. In the case of a conflict or abnormal shutdown, an unlogged table is automatically truncated. The content of unlogged tables is not backed up to the standby node, and no log is automatically recorded when an index is created for unlogged tables.

  4. Configure Options.

    • IF NOT EXISTS: Create the table only if a table with same name does not exist.
    • WITH OIDS: Create a table and assign OIDs.
    • Configure Filler Factor. The value range is 10 to 100. The default value is 100 (filled to capacity).

    If Fill Factor is set to a smaller value, the INSERT operation fills only the specified percentage of a table page. The free space of the page will be used to update rows on the page. In this way, the UPDATE operation can place the updated row content on the original page, which is more efficient than placing the update on a different page. Set it to 100 for a table that has never been updated. Set it to a smaller value for largely updated tables. TOAST tables do not support this parameter.

  5. Enter table description in the Description of Table text box.
  6. Click Next to define the column information of the table.

You can configure the following parameters of a common table:

Table 1 Parameters

Parameter

Row-store Table

Column-store Table

ORC Table

Table Type

x

If Not Exists

With OIDS

x

x

Fill Factor

x

x

Defining a Column

A column defines a unit of information within a table's row. Each row is an entry in the table. Each column is a category of information that applies to all rows. When you add a table to a database, you can define the columns that compose it. Columns determine the type of data that the table can hold. After providing the general information about the table, click the Columns tab to define the list of table columns. Each column contains name, data type, and other optional properties.

You can perform the following operations only in a common table:

To define a column, perform the following steps:

  1. Enter the column name in Column Name field. It specifies the name of a column to be created in the new table. This must be a unique name in the table.

    NOTE:

    Select the Case check box to retain the capitalization of the value of the Column Name parameter. For example, if the column name entered is "Name", then the column name is created as "Name".

  2. Configure Array Dimensions. It specifies the array dimensions for the column.

    Example: If array dimension for a column is defined as integer [], then it will add the column data as single dimension array.

    The marks column in the above table was created as single dimension and subject column as two dimensions.

  3. Select the data type of the column from the Data Type drop-down list. For example, bigint for integer values.

    For complex data types,

    • Select the required schema from the Data type Schema drop-down list.
    • Select the corresponding data type from the Data Type drop-down list. This list displays the tables and views for the selected schema.
      NOTE:

      User-defined data types are not available for selection.

  4. Enter the precision/size value of the data type entered in the Precision/Size field. This parameter is valid only when the data type can be defined by precision or size.
  5. Select the scale of the data type entered in the Scale field.
  6. Choose the following Column Constraints if required:

    • NOT NULL: The column cannot contain null values.
    • UNIQUE: The column may contain only unique values.
    • DEFAULT: The default value used when no value is defined for the column.
    • Check: An expression producing a Boolean result, which new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

  7. To add comments to Column in the Create Regular Table dialog box, add column information in Description of Column (Max 5000 chars) text box and click Add. You can also add comments in the column addition dialog box. You can check comments in general table properties.
  8. After you enter all information for new column, click Add. You can also delete a column from a list or change the order of columns. After defining all columns, click Next.

You can configure the following parameters of a column in a common table:

Table 2 Parameters

Parameter

Row-store Table

Column-store Table

ORC Table

Array Dimensions

x

x

Data Type Schema

x

x

NOT NULL

Default

UNIQUE

x

x

CHECK

x

x

Deleting a Column

To delete a column, perform the following steps:

  1. Select a column.
  2. Click Delete.

Editing a Column

Follow the steps to edit a column:

  1. Select a column.
  2. Click Edit.
  3. Edit the column details as required and click Update to save changes.

    NOTE:

    You must complete the edit operation and save the changes to continue with other operations.

Moving a Column

You can move a column in a table. To move a column, select the column and click Up or Down.

Selecting Data Distribution

Data distribution specifies how the table is distributed or replicated among data nodes.

Select one of the following options for the distribution type:

Distribution Type

Description

DEFAULT DISTRIBUTION

The default distribution type will be assigned for this table.

REPLICATION

Each row of the table will be replicated in all the data nodes of the database cluster.

HASH

Each row of the table will be placed based on the hash value of the specified column.

RANGE

Each row of the table will be placed based on the range value.

LIST

Each row of the table will be placed based on the list value.

After selecting data distribution, click Next.

The following table lists the data distribution parameters that can be configured for common tables.

Table 3 Distribution types

Distribution Type

Row-store Table

Column-store Table

ORC Table

DEFAULT DISTRIBUTION

x

HASH

REPLICATION

x

Defining Table Constraints

Creating constraints is optional. A table can have one (and only one) primary key. Creating the primary key is a good practice.

You can select the following types of constraints from the Constraint Type drop-down list:

Primary Key

The primary key is the unique identity of a row and consists of one or more columns.

Only one primary key can be specified for a table, either as a column constraint or as a table constraint. The primary key constraint must name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table.

Set the constraint type to PRIMARY KEY and enter the constraint name. Select a column from the Available Columns list and click Add. If you need a multi-column primary key, repeat this step for another column.

Fill Factor for a table is in the range 10 and 100 (unit: %). The default value is 100 (filled to capacity). If Fill Factor is set to a smaller value, the INSERT operation fills only the specified percentage of a table page. The free space of the page will be used to update rows on the page. In this way, the UPDATE operation can place the updated row content on the original page, which is more efficient than placing the update on a different page. Set it to 100 for a table that has never been updated. Set it to a smaller value for largely updated tables. TOAST tables do not support this parameter.

DEFERRABLE: Defer an option.

INITIALLY DEFERRED: Check the constraint at the specified time point.

In the Constraints area, click Add.

You can click Delete to delete a primary key from the list.

Mandatory parameters are marked with asterisks (*).

UNIQUE

Set the constraint type to UNIQUE and enter the constraint name.

Select a column from the Available Columns list and click Add. To configure unique for multiple columns, repeat this step for another column. After adding the first column, the UNIQUE constraint name will be automatically filled. The name can be modified.

Fill Factor: For details, see Primary Key.

DEFERRABLE: For details, see Primary Key.

INITIALLY DEFERRED: For details, see Primary Key.

You can click Delete to delete UNIQUE from the list.

Mandatory parameters are marked with asterisks (*).

CHECK

Set the constraint type to CHECK and enter the constraint name.

When the INSERT or UPDATE operation is performed, and if the check expression fails, then table data is not altered.

If you double-click column in Available Columns list, it is inserted to Check Expression edit line to current cursor position.

In the Constraints area, click Add. You can click Delete to delete CHECK from the list. Mandatory parameters are marked with asterisks (*). After defining all constraints, click Next.

The following table lists the table constraint parameters that can be configured for common tables.

Table 4 Constraint types

Constraint Type

Row-store Table

Column-store Table

ORC Table

CHECK

x

x

UNIQUE

x

x

PRIMARY KEY

x

x

Defining an Index

Indexes are optional. They are used to enhance database performance. This operation constructs an index on the specified column(s) of the specified table. Select the Unique Index check box to enable this option.

Choose the name of the index method from the Access Method list. The default method is B-tree.

The Fill factor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fill factor of 90, but any integer value from 10 to 100 can be selected. If the table is static, then a fill factor of 100 can minimize the index's physical size. For heavily updated tables, an explain plan smaller fill factor is better to minimize the need for page splits. Other indexing methods use different fill factors but work in similar ways. The default fill factor varies between methods.

You can either enter a user-defined expression for the index or you can create the index using the Available Columns list. Select the column in the Available Columns list and click Add. If you need a multi-column index, repeat this step for other columns.

After entering the required information for the new index, click Add.

You can also delete an index from the list using the Delete button. After defining all indexes, click Next.

You can configure the following parameters of an index in a common table.

Table 5 Parameters

Parameter

Row-store Table

Column-store Table

ORC Table

Unique Indexes

x

x

btree

x

gin

x

gist

x

hash

x

psort

x

spgist

x

Fill Factor

x

x

User Defined Expression

x

x

Partial Index

x

x

SQL Preview

Data Studio generates a DDL statement based on the inputs provided in Create New table wizard.

You can only view, select, and copy the query. You cannot edit the query.

  • To select all queries, press Ctrl+A or right-click and select Select All.
  • To copy the selected query, press Ctrl+C or right-click and select Copy.

Click Finish to create the table. On clicking the Finish button, the generated query will be sent to the server. Any errors are displayed in the dialog box and status bar.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback