VMware Greenplum 7 retains most aspects of the partitioning syntax of prior versions of Greenplum, now referred to as the classic partitioning syntax. Version 7 also introduces support for the PostgreSQL declarative partitioning syntax, the modern syntax.
This topic describes the Greenplum 7 partitioning syntax and behavior changes, and is geared toward existing users of Greenplum 6.
(Refer to Partitioning Large Tables for information about using both syntaxes to create and manage partitioned tables.)
Parent topic: Defining Database Objects
The following partitioning-related features are new in Greenplum 7:
Support for PostgreSQL declarative partitioning syntax, which introduces these new features for the modern syntax only:
Less restrictive locking in
ATTACH PARTITION. You can now attach a partition to a partition hierarchy without disrupting many normal query executions on the partition.
GRANT ... ONLY and
REVOKE ... ONLY syntaxes that direct Greenplum to apply the operation to the named table only (does not recurse the operation to child partition tables).
ALTER TABLE ONLY syntax that directs Greenplum to apply the operation to the named table only (does not recurse the operation to child partition tables).
The following classic partitioning syntax and behaviors have not changed in Greenplum 7:
REVOKEoperation on a parent partitioned table recurses to its child partition tables.
ALTER TABLEoperation on a parent partitioned table recurses to its child partition tables.
The following items describe the Greenplum 7 changes to classic partitioning syntax and behaviors compared to Greenplum 6:
New internal data structures and catalogs are used to represent partitioned tables.
Greenplum 7 represents and operates on partitioned tables using the new internal data structures and catalog, regardless of the partitioning syntax used to create the table. Operations that you invoke using the classic partitioning syntax are internally mapped to the new data structures, and any output generated by Greenplum, such as DDL, is displayed in that format.
These partitioning-related catalog tables, views, and functions are removed:
The new pg_partitioned_table catalog table and
pg_partition_root() functions to provide similar information. Refer to About Viewing Your Partition Design for more information on these new functions.
FOR (RANK(<value>)) clause is no longer supported. When creating or altering a partitioned table, you must locate a partition by
Partition boundaries are no longer represented as
CHECK constraints, but rather internally-constructed partition constraints.
The interpretation of
INCLUSIVE clauses for range partition boundaries has changed:
START EXCLUSIVE <n>is specified, Greenplum now implicitly converts this to a
START INCLUSIVE <n>+1.
END INCLUSIVE <n>is specified, Greenplum now implicitly converts this to an
END EXCLUSIVE <n>+1.
END INCLUSIVEboundaries are now permitted only for data types that have a suitable
Partition-specific information displayed in
\d+ output has changed:
Partition boundaries are now tagged
Inherits, the parent table is now identified with the tag
Range partition boundaries defined with
END are displayed using the (modern syntax)
FOR VALUES [ FROM/TO | IN | WITH ] keywords.
\d+ jan_sales Table "public.jan_sales" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |--------+---------------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | date | date | | | | plain | | amt | numeric(10,2) | | | | main | | Partition of: sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01') Partition constraint: ((date IS NOT NULL) AND (date >= '2023-01-01'::date) AND (date < '2023-02-01'::date)) Distributed by: (id)
EXCHANGE PARTITION command has changed:
ATTACH PARTITIONcommands introduced with the modern syntax.
Property inheritance changes:
SPLIT PARTITIONinherit the properties of the split child.
Additional factors to consider: