Partition table by year

Moderator: NorbertKrupa

Post Reply
Timbo
Intermediate
Intermediate
Posts: 53
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Partition table by year

Post by Timbo » Tue Jan 20, 2015 6:02 pm

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

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Partition table by year

Post by id10t » Tue Jan 20, 2015 7:32 pm

Hi!

[DELETED]
Last edited by id10t on Mon May 04, 2015 8:59 pm, edited 1 time in total.

Timbo
Intermediate
Intermediate
Posts: 53
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Re: Partition table by year

Post by Timbo » Thu Jan 22, 2015 11:20 am

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

Post Reply

Return to “Vertica Performance Tuning”