CREATE TABLE PARTITION AS
Description
Creates a partitioned table from the results of a query.
CREATE TABLE PARTITION AS creates a partitioned table and fills it with data obtained using SELECT. Data is partitioned based on the specified partitioning policy. The table columns have the names and data types associated with the output columns of SELECT (except that you can override the SELECT output column names by giving an explicit list of new column names).
Syntax
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE [ IF NOT EXISTS ] partition_table_name [ (column_name [, ...] ) ] [ { ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } } [ [ , ] ... ] ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | { [HASH ] ( column_name ) } } ] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ] PARTITION BY partition_clause partition_definition_list AS query [ WITH [ NO ] DATA ]; |
For details about each column, see Syntax.
Parameters
- IF NOT EXISTS
When IF NOT EXISTS is specified, the system checks whether a relationship with the same name already exists in the current schema before creating a table. It is not created and a NOTICE is returned if a relationship with the same name already exists. When IF NOT EXISTS is not specified and a relationship with the same name exists in the schema, an ERROR is returned.
- partition_table_name
Specifies the name of the partitioned table to be created.
Value range: a string that complies with the Identifier Naming Conventions.
- column_name
Optional. Specifies the name of a column to be created in the new table. If no column name is specified, the columns in the new table are the same as those entered in the SELECT statement.
Value range: a string that complies with the Identifier Naming Conventions.
- ENGINE
Supported in MySQL-compatible mode and used only for syntax adaptation. Only InnoDB can be set and no actual effect is achieved.
- WITH ( storage_parameter [= value] [, ... ] )
Specifies an optional storage parameter for a table or an index. See details of parameters below.
- FILLFACTOR
The fill factor of a table is a percentage from 10 to 100. If the Ustore is used, the default value is 92. 100 (complete filling) is the default value. When a smaller fill factor is specified, INSERT operations fill table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fill factor to 100 (complete filling) is the best choice, but in heavily updated tables a smaller fill factor would be appropriate. The parameter is only valid for row–store tables.
Value range: 10–100
- ORIENTATION
ROW (default value): The data will be stored in rows.
- COMPRESSION
Specifies the compression level of table data. It determines the compression ratio and time. Generally, the higher the level of compression, the higher the ratio, the longer the time; and the lower the level of compression, the lower the ratio, the shorter the time. The actual compression ratio depends on the distribution mode of table data loaded.
Value range:
This parameter is available only to column-store compression.
- autovacuum_enabled
Specifies whether the autovacuum function is enabled for the table.
Value range: on and off
Default value: on
- autovacuum_vacuum_threshold
Specifies the minimum number of tuples required to be updated or deleted in the table to trigger VACUUM for the autovacuum function. It takes effect only for Astore tables.
Value range: 0 to 2147483647
The default value is –1, which is the same as the value of the GUC parameter autovacuum_vacuum_threshold.
- autovacuum_analyze_threshold
Specifies the minimum number of tuples required to be inserted, updated, or deleted in the table to trigger ANALYZE for the autovacuum function.
Value range: 0 to 2147483647
The default value is –1, which is the same as the value of the GUC parameter autovacuum_analyze_threshold.
- autovacuum_vacuum_scale_factor
Specifies the ratio of tuples required to be inserted, updated or deleted in the table to trigger VACUUM for the autovacuum function. It takes effect only for Astore tables.
Value range: 0.0 to 100.0
The default value is –1, which is the same as the value of the GUC parameter autovacuum_vacuum_scale_factor.
- autovacuum_analyze_scale_factor
Specifies the ratio of tuples required to be inserted, updated or deleted in the table to trigger ANALYZE for the autovacuum function.
Value range: 0.0 to 100.0
The default value is –1, which is the same as the value of the GUC parameter autovacuum_analyze_scale_factor.
- autovacuum_freeze_min_age
Specifies the minimum age of a row version for the autovacuum function. Only rows older than the minimum age are frozen.
Value range: 0 to 1000000000
The default value is –1, which is the same as the value of the GUC parameter vacuum_freeze_min_age.
- autovacuum_freeze_max_age
Specifies the number of transactions after which the VACUUM operation is forcibly performed for the pg_class.relfrozenxid column in the table for the autovacuum function. The system starts the AUTOVACUUM process even if autovacuum is disabled. The autovacuum operation also allows old files to be deleted from the pg_clog/ subdirectory. It takes effect only for Astore tables.
Value range: 100000 to 2000000000
The default value is –1, which is the same as the value of the GUC parameter autovacuum_freeze_max_age.
- autovacuum_freeze_table_age
Specifies the amount of time that a table remains unchanged if it is marked as not requiring autovacuum for the autovacuum function. It takes effect only for Astore tables.
Value range: 0 to 2000000000
The default value is –1, which is the same as the value of the GUC parameter vacuum_freeze_table_age.
- hashbucket
Creates a hash table that uses buckets. This parameter supports only row-store tables, including row-store range tables.
Value range: on and off
Default value: off
In current version, DDL operations on hash bucket tables are affected. Therefore, you are advised not to frequently perform DDL operations on hash bucket tables.
- FILLFACTOR
- [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]]
When creating a table, you can call ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW to add an advanced compression policy for row store.
- AFTER n { day | month | year } OF NO MODIFICATION: indicates the rows that are not modified in n days, months, or years.
- ON (EXPR): indicates the row-level expression, which is used to determine whether a row is hot or cold.
Compatibility parameters may affect the output of some functions supported by the ON(EXPR) row-level expression of the ILM policy. For example, after b_format_version and b_format_dev_version are set to '5.7' and 's2' respectively for the upper function in MySQL-compatible mode, uppercase conversion will be unavailable.
- TABLESPACE tablespace_name
Specifies that the new table will be created in the tablespace_name tablespace. If not specified, the default tablespace is used.
- DISTRIBUTE BY
For details, see DISTRIBUTE BY.
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP specifies the node group to which the table to be created belongs. TO NODE is used for internal scale-out tools.
- PARTITION BY
For details about the columns of partition_clause and partition_definition_list, see Parameters.
- AS query
Specifies a SELECT or VALUES command, or an EXECUTE command that runs a prepared SELECT or VALUES query.
- [ WITH [ NO ] DATA ]
Specifies whether the data produced by the query should be copied to the new partitioned table. By default, the data will be copied. If the value NO is used, only the table structure will be copied.
Example
-- Create source table t1 and insert three data records. gaussdb=# CREATE TABLE t1(a int, b int) DISTRIBUTE BY HASH(a); gaussdb=# INSERT INTO t1 VALUES(8, 2); gaussdb=# INSERT INTO t1 VALUES(18, 3); gaussdb=# INSERT INTO t1 VALUES(28, 4); -- Create a level-1 partitioned table using CREATE TABLE PARTITION AS and fill it with data obtained through subqueries. gaussdb=# CREATE TABLE t1_part_dup DISTRIBUTE BY HASH(a) PARTITION BY RANGE(a) ( PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(20), PARTITION p3 VALUES LESS THAN(MAXVALUE) ) AS SELECT * FROM t1; gaussdb=# \d+ t1_part_dup; Table "create_table_as_partition_by.t1_part_dup" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- a | integer | | plain | | b | integer | | plain | | Partition By RANGE(a) Number of partitions: 3 (View pg_partition to check each partition range.) Has OIDs: no Distribute By: HASH(a) Location Nodes: ALL DATANODES Options: orientation=row, compression=no gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p1) ORDER BY a; a | b ---+--- 8 | 2 (1 row) gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p2) ORDER BY a; a | b ----+--- 18 | 3 (1 row) gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p3) ORDER BY a; a | b ----+--- 28 | 4 (1 row) gaussdb=# DROP TABLE t1, t1_part_dup;
Helpful Links
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot