PROJECTION_COLUMNS

Provides information about projection columns, such as encoding type, sort order, type of statistics, and the time at which columns statistics were last updated.

Column Name Data Type Description
PROJECTION_ID INTEGER

Catalog-assigned numeric value that uniquely identifies the projection.

PROJECTION_NAME VARCHAR

The projection name for which information is listed.

PROJECTION_COLUMN_NAME VARCHAR

The projection column name.

COLUMN_POSITION INTEGER

The ordinal position of a projection's column used in the CREATE PROJECTION statement.

SORT_POSITION INTEGER

The projection's column sort specification, as specified in CREATE PROJECTION .. ORDER BY clause. If the column is not included in the projection's sort order, SORT_POSITION output is NULL.

COLUMN_ID INTEGER

A unique numeric object ID (OID) that identifies the associated projection column object and is assigned by the Vertica catalog. This field is helpful as a key to other system tables.

DATA_TYPE VARCHAR

Matches the corresponding table column data type (see V_CATALOG.COLUMNS). DATA_TYPE is provided as a complement to ENCODING_TYPE.

ENCODING_TYPE VARCHAR

The encoding type defined on the projection column.

ACCESS_RANK INTEGER

The access rank of the projection column. See the ACCESSRANK parameter in the CREATE PROJECTION statement for more information.

GROUP_ID INTEGER

A unique numeric ID (OID) that identifies the group and is assigned by the Vertica catalog.

TABLE_SCHEMA VARCHAR

The name of the schema in which the projection is stored.

TABLE_ID INTEGER

Catalog-assigned numeric value that uniquely identifies the table.

TABLE_NAME VARCHAR

The table name that contains the projection.

TABLE_COLUMN_ID VARCHAR

Catalog-assigned VARCHAR value that uniquely identifies a table column.

TABLE_COLUMN_NAME VARCHAR

The projection's corresponding table column name.

STATISTICS_TYPE VARCHAR

The type of statistics the column contains:

STATISTICS_UPDATED_TIMESTAMP

TIMESTAMPTZ

The time at which the columns statistics were last updated by ANALYZE_STATISTICS. By querying this column, along with STATISTICS_TYPE and PROJECTION_COLUMN_NAME, you can identify projection columns whose statistics need updating. See also system table PROJECTIONS.

IS_EXPRESSION BOOLEAN

Indicates whether this projection column is calculated with an expression. For aggregate columns, IS_EXPRESSION is always true.

IS_AGGREGATE BOOLEAN

Indicates whether the column is an aggregated column in a live aggregate projection. IS_AGGREGATE is always false for Top-K projection columns.

PARTITION_BY_POSITION INTEGER Position of that column in the PARTITION BY and GROUP BY clauses, if applicable.
ORDER_BY_POSITION INTEGER Set only for Top-K projections, specifies the column's position in the ORDER BY clause, as defined in the projection definition's window partition clause. If the column is omitted from the ORDER BY clause, ORDER_BY_POSITION output is NULL.
ORDER_BY_TYPE INTEGER

Type of sort order:

  • ASC NULLS FIRST
  • ASC NULLS LAST
  • DESC NULLS FIRST
  • DESC NULLS LAST
COLUMN_EXPRESSION VARCHAR Expression that calculates the column value.

Examples

See Statistics Data in PROJECTION_COLUMNS

See Also