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.

Limits projection data to a range of partition keys. The minimum value must be less than or equal to the maximum value.

Values can be NULL. A null minimum value indicates no lower bound and a null maximum value indicates no upper bound. If both are NULL, this statement drops the projection endpoints, producing a regular projection instead of a range 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