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

Comments

• SruthiA Vertica Employee Administrator

  July 2015

  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

  0
• Igor_Trosko ✭

  July 2015

  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'?

  0
• SruthiA Vertica Employee Administrator

  July 2015

  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

   

   

  0
• Igor_Trosko ✭

  July 2015

  Thanks very much!

  0