Cannot use meta function or non-deterministic function in PARTITION BY expression

Igor_Trosko

Hi all,

 

I want to create table, partitioned by days of month. I've tried alter table MY_TABLE_NAME partition by extract(DAY from starttime)::INT reorganize; but had ROLLBACK 2552:  Cannot use meta function or non-deterministic function in PARTITION BY expression. What is the problem? Thanks!
 

SruthiA

Hi,

 

   Can you check the data type of starttime? Is it timestamptz? if so, changing it to timestamp will work. Since timestamptz value changes according to the locale. All the expressions in Paritition by clause must be immutable. meaning that they return the exact same value regardless of when they are invoked, and independently of session or environment settings, such as LOCALE.

 

 

-Regards,

 Sruthi

Igor_Trosko

Thank you! You are right, it's timestamp with timezone. Do you meant something like this?

CREATE TABLE public.test (

     date      TIMESTAMPTZ NOT NULL

) PARTITION BY EXTRACT(year FROM date AT TIME ZONE 'UTC');

 

Maybe I can point only keyword LOCALE without specific value like 'UTC'?

SruthiA

Hi,

 

   Yeah, I meant the create table should be like the following

 

CREATE TABLE public.test (

     date      TIMESTAMPTZ NOT NULL

) PARTITION BY EXTRACT(year FROM date AT TIME ZONE 'UTC');

 

 

No, You cannot use the keyword LOCALE in the create table statement, since it is a environment setting and not specific to table. For more information on how to use TIME ZONE, please refer to 

 

https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMPATTIMEZONE.htm%3FTocPath%3DSQL%2520Reference%2520Manual%7CSQL%2520Data%2520Types%7CDate%252FTime%2520Data%2520Types%7C_____8

 

-Regards,

 Sruthi

 

 

Igor_Trosko

Thanks very much!