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 }

Parameters

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.

new‑name

The new projection name.

ON PARTITION RANGE

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.

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.

Privileges

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

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