Quarterly partition non-deterministic error

Navin_CNavin_C Vertica Customer
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

Comments

  • Navin_CNavin_C Vertica Customer
    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file