Partitioning table on year and week of the year

Hdk

alter table TABLE_NAME partition by EXTRACT(YEAR FROM to_timestamp(CC)); ROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression alter table TABLE_NAME partition by date_part('YEAR',to_timestamp(CC)); ROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression

Data in CC column is like 1441650600 EPOCH time

Does anyone knowsthe reason?

eli_revach

Hi ,

This should work for you :

 

alter table TABLE_NAME partition by ( (date_part('year', TIMESTAMPADD('ss',CC,TIMESTAMP 'epoch' )) * 100) + date_part('week', TIMESTAMPADD('ss',CC,TIMESTAMP 'epoch' )));

 

I hope you will find it useful 

 

Thanks .

Hdk

Thank you so much . It worked as expected.