Partitioned Tables（2）
Tags: opengauss
Published: 2023-03-07 06:31:14

**Example of Creating a List Partitioned Table**

Example 3: Create the **graderecord** list partitioned table. There are four partitions, and their partition keys are of the CHAR type. The partition ranges are 21.01, 21.02, 21.03, and 21.04.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/da3m1phf2s4olj0jxjv7.png)

openGauss=# insert into graderecord values('210101','Alan','21.01',92);  
openGauss=# insert into graderecord values('210102','Ben','21.01',62);  
openGauss=# insert into graderecord values('210103','Brain','21.01',26);  
openGauss=# insert into graderecord values('210204','Carl','21.02',77);  
openGauss=# insert into graderecord values('210205','David','21.02',47);  
openGauss=# insert into graderecord values('210206','Eric','21.02',97);  
openGauss=# insert into graderecord values('210307','Frank','21.03',90);  
openGauss=# insert into graderecord values('210308','Gavin','21.03',100);  
openGauss=# insert into graderecord values('210309','Henry','21.03',67);  
openGauss=# insert into graderecord values('210410','Jack','21.04',75);  
openGauss=# insert into graderecord values('210311','Jerry','21.04',60);

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zwe6exu865q89st0y3sd.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dy8fjlb756q38wjj4vfq.png)

**Syntax for Creating an Interval Partitioned Table**

Interval partitioning adds the definition of the interval value **PARTITION BY RANGE (partition_key)** on the basis of range partitioning.

The syntax of the **VALUES LESS THAN** interval partition is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ijgwn8mftjrvhgf5301f.png)

The syntax for creating a START END interval partitioned table is as follows:

Method 1: By executing **START(partition_value) END (partition_value | MAXVALUE)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dqqrqkzoe4yjv563lcdg.png)

Method 2: By executing **START(partition_value) END (partition_value) EVERY (interval_value)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mdn8jo26d0rgrjp2x4fb.png)

Method 3: By executing **START(partition_value)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mxk1gt4rzhns6uyagkcn.png)

Mwthod 4: By executing **END(partition_value | MAXVALUE)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jkfcuovfquafe6fc98aw.png)

**Parameters for Creating an Interval Partitioned Table**

**· INTERVAL ('interval_expr')**

Defines interval partitioning. Only the TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE] and DATE data types are supported.

**· interval_expr**

Specifies the interval for automatically creating partitions, for example, 1 day or 1 month.

**· partition_name**

Specifies the name of a range partition.

The partitions automatically created by the system are named **sys_p1**, **sys_p2**, **sys_p3**, and the like.

**Syntax for Creating a Hash Partitioned Table**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4vjcn43u8y24rshkdvhc.png)

**Parameters for Creating a Hash Partitioned Table**
**· partition_table_name**

Specifies the name of the partitioned table.

**· column_name**

Specifies the name of the column to be created in the new table.

**· data_type**

Specifies the data type of the column.

**· partition_key**

Specifies the name of the partition key. The hash partitioning policy supports only one column of partition keys.

**· partition_name**

Specifies the name of a hash partition. The number of hash partitions to be created is the same as the number of partition names.

**Example of Creating a Hash Partitioned Table**
Example 4: Create the hash_partition_table hash partitioned table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8rz6kuokdw8x90zvwolw.png)

**Syntax for Importing Data**

Import a single row of data.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z7d104znha77ovqjsze8.png)

Import data from an existing table with the same structure.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/majjsm11s9xtad7yqlwf.png)

**Parameters for Importing Data**

**· partition_table_name**

Specifies the name of the partitioned table.

**· column_name**

Specifies a column name in the partitioned table. It can be omitted.

**· value**

Specifies column values.

· If the value of **column_name** is provided, the value provided by the **value** clause is associated with the corresponding column from left to right.
· If the value of **column_name** is not provided, the value provided by the **value** clause is associated with the column specified by **partition_table_name** from left to right.

**Example of Importing Data**

Example 5：

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s83ittizdgr6kh0ay8od.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0axfykkihcqq23v59jrf.png)

**Syntax for Modifying a Partitioned Table**
**·** Delete a partition.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vjx8fday9nbb8kscyomg.png)

**·** Add a partition.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wistv4hmjnzpogpmk1ql.png)

**·** Rename a partition.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/32dck9tavogcjhjcwak6.png)

**·** Split a partition (Specify the syntax of split_partition_value).

ALTER TABLE partition_table_name SPLIT PARTITION partition_name AT ( split_partition_value ) INTO ( PARTITION partition_new_name1, PARTITION partition_new_name2); 

**·** Split a partition (Specify the syntax of the partition range).

ALTER TABLE partition_table_name SPLIT PARTITION partition_name INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }; 

**·** Combine partitions.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fj0b57yeksvw5blmqaec.png)

**Parameters for Modifying a Partitioned Table**
**· partition_table_name**

Specifies the name of the partitioned table.

**· partition_name**

Specifies the partition name.

**· split_partition_value**

Specifies the split point.

**· PARTITION partition_new_name1, PARTITION partition_new_name2**

Specifies the two partitions that are split based on the split point.

**· partition_less_than_item**

Specifies the description statement of a partition item. The syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h6rq6zot32bqys4l9ruo.png)

The usage is the same as that in Syntax for Creating a VALUES LESS THAN Range Partitioned Table.

**· partition_start_end_item**

Specifies the description statement of a partition item. The syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y2fjnc0ufxuit8b9jxpy.png)

The usage is the same as that in Syntax for Creating a START END Range Partitioned Table.

**· partition_list_item**

Specifies the description statement of a partition item. The syntax is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x3vieeu7ituyc2dymeu6.png)

The usage is the same as that in Syntax for Creating a List Partitioned Table.

**· split_point_clause**

Specifies the split point when a partition is split.

**· partition_value**

Specifies the key value of a partition.

**Syntax for Deleting a Partitioned Table**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/62i08mjrn1c1a4ovclic.png)

**Parameters for Deleting a Partitioned Table**
**· partition_table_name**

Specifies the name of the partitioned table.

**Example of Deleting a Partitioned Table**
Example 6:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/87yb8dbalqqk1lfg9gjm.png)