1

I am trying to create a partition for the last 30 days. Yet when I run this code:

create table Test_Older30day partition of Test
    for values from (current_timestamp + interval'-30 day') to (current_timestamp);

I get this error:

syntax error at or near "current_timestamp"

I am confused, what am I doing wrong here?

the table looks like this:

create table if not exists Test(
source_name varchar,
event_name varchar,
service_data json,
stamp timestamptz
)partition by range(stamp);

data types match and I have used the partition by range on the date field.

I already know I can insert them manually

create table Test_Older30day partition of Test
        for values from ('2019-08-11') to ('2019-09-11')

like that. What I am trying to attempt is a more automated way of doing it. Basically the idea is to avoid typing it every single month or week. Just to run the query and it would use the current date.

  • I think the edge points should be constant, you can't use current_timestamp. – ypercubeᵀᴹ Sep 11 '19 at 16:05
  • @ypercubeᵀᴹ How would I go about making them constant? I've tried everything from creating a function with 2 declared variables one for start the other for end date. And it still didn't work. And I tried "declaring" variables using with clause. But there is no syntax I know of that allows stuff like create table x partition of y as with clause I tried searching that to no avail. – Chessbrain Sep 11 '19 at 16:08
  • I already know I can insert them manually '2019-09-11' like that. What I am trying to attempt is a more automated way of doing it. Basically the idea is to avoid typing it every single month or week. Just to run the query and it would use the current date. – Chessbrain Sep 11 '19 at 16:13
  • 1
    (Comment above was: *Something like: for values from ('2019-08-12') to ('2019-09-12')) But for automated way as you describe, you probably need dynamic SQL then. Please add your comment in the question. – ypercubeᵀᴹ Sep 11 '19 at 16:14
  • 1
    Your syntax will work in v12. Until then, the bounds must be literals, not expressions. Even in v12, what you write is unlikely to do what you want, as you almost surely want your bounds aligned with some granularity above the nanosecond level. – jjanes Sep 11 '19 at 19:03
2

what am I doing wrong here?

You are incorrectly using non-literal values for partition bound specifications. Documentation states that partition_bound_spec is

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

that is, only literals or special keywords MINVALUE and MAXVALUE are allowed. current_timestamp is a function, not a literal value.

As suggested in the comments, you will need to generate your create table ... partition ... statement dynamically if you want to use the function return value as the partition bound.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.