ALTER PROJECTION

Changes the DDL of the specified projection.

Syntax

ALTER PROJECTION [[database.]schema.]projection
   { RENAME TO new-name | ON PARTITION RANGE BETWEEN min-val AND max-val | { ENABLE | DISABLE } }
 

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

projection

The projection to change, where projection can be one of the following:

• Projection base name: Rename all projections that share this base name.

• Projection name: Rename the specified projection and its base name. If the projection is segmented, its buddies are unaffected by this change.

See Projection naming for projection name conventions.

RENAME TOnew-name

The new projection name.

ON PARTITION RANGE

Note

Valid only for projections that were created with a 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.

If the new range of keys is outside the previous range, Vertica throws a warning that the projection is out of date and must be refreshed before it can be used.

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

ENABLE | DISABLE

Specifies whether to mark this projection as unavailable for queries on its anchor table. If a projection is the queried table's only superprojection, attempts to disable it return with a rollback message. ENABLE restores the projection's availability to query planning. You can also mark a projection as unavailable for individual queries using the hint SKIP_PROJS.

Default: ENABLE

Privileges

Non-superuser, CREATE and USAGE on the schema and one of the following anchor table privileges:

• Table owner

• ALTER privilege

• SELECT privilege only if defining a partition range projection

Syntactic sugar

The statement

=> ALTER PROJECTION foo REMOVE PARTITION RANGE;

has the same effect as

=> ALTER PROJECTION foo ON PARTITION RANGE BETWEEN NULL AND NULL;

Examples

=> SELECT export_tables('','public.store_orders');

                export_tables
---------------------------------------------

CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date NOT NULL
);
(1 row)

=> CREATE PROJECTION store_orders_p AS SELECT * from store_orders;
CREATE PROJECTION
=> ALTER PROJECTION store_orders_p RENAME to store_orders_new;
ALTER PROJECTION
=> ALTER PROJECTION store_orders_new DISABLE;
=> SELECT * FROM store_orders_new;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection store_orders_new not used in the plan because the projection is disabled.
=> ALTER PROJECTION store_orders_new ENABLE;

See also