Tips & Tricks

Error 2552 When Partitioning With TIMESTAMPTZ

0

This quick tip shows how to work around Error 2552 when trying to partition on a TIMESTAMPTZ (timestamp at time zone).

In this table definition, the partition is defined on the year:

This throws the error:

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

Although not obvious, there is a lack of timezone with the TIMESTAMPTZ type. The "non-deterministic" in this case seems to imply that without specifying a time zone, there is no guarantee that the same result will be returned each time the partition is used.

The following works when using a TIMESTAMP without time zone:

To resolve the error and partition without issues, explicitly add the time zone in the partition clause:

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: