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

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.

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:

• Table owner

• ALTER privilege

• SELECT privilege only if defining a partition range projection

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

See also