Quarterly partition non-deterministic error

Navin_C

Hello All,

I am trying to change table's partition expression from monthly to quarterly.

The  column on which the partition is made is an integer column having value like
20120230

I tried this query to extract the quaerter for each year and see my partition_key

select distinct(partition_key) from (select cast((EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) as varchar) as partition_key from xx.xxxx) sub

Got result as was expecting

201104
201203


But now I try to implement the same while changing partition for a table this way

ALTER TABLE xx.xxxx PARTITION BY (EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) ;

I get this error

NOTICE 4954:  The new partitioning scheme will produce 3 partitionsROLLBACK 2552:  Cannot use meta function or non-deterministic function in PARTITION BY expression

What is  a non-deterministic function and any other way I can achieve quarterly partition on my data.

Thanks

Navin_C

Found the solution to this.

The ALTER partition statement should be like this.

ALTER TABLE xx.xxxx PARTITION BY cast(substring(cast(xx as varchar) , 1,4) as int)*100 + ceil(cast(substring(cast(xx as varchar) , 5,2)as int)/3)

I observed that PARTITION by clause does not work or gives an error while performing casting to date column on the column the EXTRACT is running. Which says it is a non-deterministic function.

There was a solution to this issue , when we cast the column to timestamp, but in my case it was date column , so the solution didn't work.

https://community.vertica.com/vertica/topics/partition_by_timestamptz_field_how

So I had to find a different way of coming up with the partition keys.

Any more workaround on this are welcomed.

Thanks