PROJECTIONS

Provides information about projections.

Provides information about projections.

Column Name Data Type Description
PROJECTION_SCHEMA_ID INTEGER A unique numeric ID that identifies the specific schema that contains the projection and is assigned by the Vertica catalog.
PROJECTION_SCHEMA VARCHAR

The name of the schema that contains the projection.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

PROJECTION_NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace that contains the projection.
PROJECTION_ID INTEGER A unique numeric ID that identifies the projection and is assigned by the Vertica catalog.
PROJECTION_NAME VARCHAR The projection name for which information is listed.
PROJECTION_BASENAME VARCHAR

The base name used for other projections:

  • For auto-created projections, identical to ANCHOR_TABLE_NAME.

  • For a manually-created projection, the name specified in the CREATE PROJECTION statement.

OWNER_ID INTEGER A unique numeric ID that identifies the projection owner and is assigned by the Vertica catalog.
OWNER_NAME VARCHAR The name of the projection's owner.
ANCHOR_TABLE_ID INTEGER The unique numeric identification (OID) of the projection's anchor table.
ANCHOR_TABLE_NAME VARCHAR The name of the projection's anchor table.
NODE_ID INTEGER A unique numeric ID (OID) for any nodes that contain any unsegmented projections.
NODE_NAME VARCHAR The names of any nodes that contain the projection. This column returns information for unsegmented projections only.
IS_PREJOIN BOOLEAN Deprecated, always set to f (false).
CREATED_EPOCH INTEGER The epoch in which the projection was created.
CREATE_TYPE VARCHAR

The method in which the projection was created:

  • CREATE PROJECTION: A custom projection created using CREATE PROJECTION.

  • CREATE TABLE: A superprojection that was automatically created when its associated table was created using CREATE TABLE.

  • ALTER TABLE: The system automatically created the key projection in response to a non-empty table.

  • CREATE TABLE WITH PROJ CLAUSE: A superprojection that was automatically created using CREATE TABLE.

  • DELAYED_CREATION: A superprojection that was automatically created when data was loaded for the first time into a new table.

  • DESIGNER: A projection created by Database Designer.

  • SYSTEM TABLE: A projection that was automatically created for a system table.

Rebalancing does not change the CREATE_TYPE value for a projection.

VERIFIED_FAULT_TOLERANCE INTEGER The projection K-safe value. This value can be greater than the database K-safety value (if more replications of a projection exist than are required to meet the database K-safety). This value cannot be less than the database K-safe setting.
IS_UP_TO_DATE BOOLEAN Specifies whether projection data is up to date. Only up-to-date projections are available to participate in query execution.
HAS_STATISTICS BOOLEAN

Specifies whether there are statistics for any column in the projection. HAS_STATISTICS returns true only when all non-epoch columns for a table or table partition have full statistics. For details, see Collecting table statistics and Collecting partition statistics.

IS_SEGMENTED BOOLEAN Specifies whether the projection is segmented.
SEGMENT_EXRESSION VARCHAR

The segmentation expression used for the projection. In the following example for the clicks_agg projection, the following values:

hash(clicks.user_id, (clicks.click_time)::date)

indicate that the projection was created with the following expression:

SEGMENTED BY HASH(clicks.user_id, (clicks.click_time)::date)

SEGMENT_RANGE VARCHAR

The percentage of projection data stored on each node, according to the segmentation expression. For example, segmenting a projection by the HASH function on all nodes results in a SEGMENT_RANGE value such as the following:

implicit range: node01[33.3%] node02[33.3%] node03[33.3%]

IS_SUPER_PROJECTION BOOLEAN Specifies whether a projection is a superprojection.
IS_KEY_CONSTRAINT_PROJECTION BOOLEAN

Indicates whether a projection is a key constraint projection:

  • t: A key constraint projection that validates a key constraint. Vertica uses the projection to efficiently enforce at least one enabled key constraint.

  • f: Not a projection that validates a key constraint.

HAS_EXPRESSIONS BOOLEAN Specifies whether this projection has expressions that define the column values. HAS_EXPRESSIONS is always true for live aggregate projections.
IS_AGGREGATE_PROJECTION BOOLEAN Specifies whether this projection is a live aggregate projection.
AGGREGATE_TYPE VARCHAR

Specifies the type of live aggregate projection:

  • GROUPBY

  • TOPK

IS_SHARED BOOLEAN Indicates whether the projection is located on shared storage.
PARTITION_RANGE_MIN VARCHAR Populated only if a projection specifies a partition range, the lowest and highest partition keys of the range.
PARTITION_RANGE_MAX
PARTITION_RANGE_MIN_EXPRESSION VARCHAR Populated only if a projection specifies partition range, the minimum and maximum range expressions as defined in the projection DDL.
PARTITION_RANGE_MAX_EXPRESSION

Examples

The following projection defines a range of orders placed since the first of the current year:

=> CREATE PROJECTION ytd_orders AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN date_trunc('year',now())::date AND NULL;

Given that range, the PARTITION_RANGE_MIN and PARTITION_RANGE_MAX columns in the PROJECTIONS table contain the following values:

=> SELECT projection_name partition_range_min, partition_range_min, partition_range_max 
    FROM projections WHERE projection_name ILIKE 'ytd_orders%';
 partition_range_min | partition_range_min | partition_range_max
---------------------+---------------------+---------------------
 ytd_orders_b1       | 2024-01-01          | infinity
 ytd_orders_b0       | 2024-01-01          | infinity
(2 rows)

The following projection defines a range of orders placed since the third quarter of last year:

=> CREATE PROJECTION q3_td AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN add_months(date_trunc('year',now()), -3)::date AND NULL;

Given that definition, the PARTITION_RANGE_MIN_EXPRESSION and PARTITION_RANGE_MAX_EXPRESSION columns in the PROJECTIONS table contain the following values:

=> SELECT projection_name, partition_range_min_expression, partition_range_max_expression 
    FROM projections WHERE projection_name ILIKE 'Q3_td%';
 projection_name |       partition_range_min_expression        | partition_range_max_expression
-----------------+---------------------------------------------+--------------------------------
 q3_td_b1        | add_months(date_trunc('year', now()), (-3)) | NULL
 q3_td_b0        | add_months(date_trunc('year', now()), (-3)) | NULL

See also

PROJECTION_COLUMNS