The Vertica Database Forums

Hi,
Trying to partition a table by year on the following column definition:-
last_updated timestamptz(6) NOT NULL,
but it fails, is there another way of doing this?

alter table public.rawzeroratecurves PARTITION BY EXTRACT (year FROM last_updated) REORGANIZE;
vsql:Partition_RAW.sql:1: NOTICE 4954: The new partitioning scheme will produce 11 partitions
vsql:Partition_RAW.sql:1: ROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression

This is Vertica version 6.1.

Regards
Tim

Hi!

[DELETED]

The following syntax appears to work ok:-

alter table public.rawzeroratecurves PARTITION BY DATE_PART('year', date(last_updated at time zone 'UTC')) REORGANIZE;
vsql:Partition_RAW1.sql:1: NOTICE 4954: The new partitioning scheme will produce 11 partitions
vsql:Partition_RAW1.sql:1: NOTICE 4785: Started background repartition table task
ALTER TABLE