PostgreSQL – Fix Syntax Error at or Near ‘current_timestamp’ While Creating Partition Table

partitioningpostgresql

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.

Best Answer

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.

Related Question