Standard projection

Stores a collection of table data in a format that optimizes execution of certain queries on that table.

Stores a collection of table data in a format that optimizes execution of certain queries on that table. For details, see Projections .

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { * | { MATCH_COLUMNS('pattern') | expression [ [AS] alias ] }[,...] }
   FROM [[database.]schema.]table [ [AS] alias]
   [ ORDER BY column-expr[,...] ]
   [ segmentation-spec ]
   [ KSAFE [ k-num ]
   [ ON PARTITION RANGE BETWEEN min-val AND max-val ] ]

Parameters

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Specifies the schema for this projection and its anchor table, where schema must be the same for both. If you specify a database, it must be the current database.

projection

Identifies the projection to create, where projection conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

projection-column

The name of a projection column. The list of projection columns must match the SELECT list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in the SELECT list.

grouped-clause

See GROUPED clause.

ENCODING encoding-type

The column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

AS SELECT

Specifies the columns or column expressions to select from the specified table:

* (asterisk)

Lists all columns in the queried tables.
MATCH_COLUMNS('pattern')

Returns the names of all columns in the queried anchor table that match pattern.
expression[[AS]alias]

Resolves to column data from the queried anchor table.

You can optionally assign an alias to each column expression and reference that alias elsewhere in the SELECT statement—for example, in the ORDER BY or segmentation clause.

Note

If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.

ORDER BY

Specifies columns from the SELECT list on which to sort the projection. The ORDER BY clause can only be set to ASC (the default). Vertica always stores projection data in ascending sort order.

If you order by a column with a collection data type (ARRAY or SET), queries that use that column in an ORDER BY clause perform the sort again. This is because projections and queries perform the ordering differently.

If you omit the ORDER BY clause, Vertica uses the SELECT list to sort the projection.

segmentation-spec

Specifies how to distribute projection data with one of the following clauses:

hash-segmentation-clause: Specifies to segment projection data evenly and distribute across cluster nodes:
```
SEGMENTED BY expression ALL NODES [ OFFSET offset ]
```
unsegmented-clause: Specifies to create an unsegmented projection:
```
UNSEGMENTED ALL NODES
```

If the anchor table and projection both omit specifying segmentation, the projection is defined with a hash segmentation clause that includes all columns in the SELECT list , as follows:

SEGMENTED BY HASH(column-expr[,...]) ALL NODES OFFSET 0;

Tip

Vertica recommends segmenting large tables.

KSAFE [k-num]

Specifies K-safety for the projection, where k-num must be equal to or greater than database K-safety. Vertica ignores this parameter if set for unsegmented projections. If you omit k-num, Vertica uses database K-safety.

For general information, see K-safety in an Enterprise Mode database.

ON PARTITION RANGE

Specifies to limit data of this projection to a range of partition keys, specified as follows:

ON PARTITION RANGE BETWEEN min-range-value AND max-range-value

where the following requirements apply to min-range-value and ≤ max-range-value:

min-range-value must be ≤ max-range-value
They must resolve to a data type that is compatible with the table partition expression.
They can be:
- String literals—for example, 2021-07-31
- Expressions with stable or immutable functions, for example:
```
date_trunc('month', now()::timestamp - interval'1 month')
```

max-range-value can be set to NULL, to specify that the partition range has no upper bound.

min-range-value can be set to NULL, to specify that the partition range has no lower bound.

If both partition range projection min-range-value and max-range-value are set to NULL, it will drop the projection endpoints, becoming a regular projection.

For other requirements and usage details, see Partition range projections.

Privileges

Non-superusers:

Anchor table owner
CREATE privilege on the schema

Examples

See: