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