PostgreSQL 11 - Getting error when trying to partition with timestamp

Question

I'm using postgres-11 on my app and I have an issue with the partitioning.

I created a partitioned table as follow:

CREATE TABLE IF NOT EXISTS MEASUREMENT (

    date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...
    CONSTRAINT MEASUREMENT_PK PRIMARY KEY (date,...)
)
PARTITION BY RANGE (date);

CREATE TABLE MEASUREMENT_P_INFINITY PARTITION OF MEASUREMENT FOR VALUES FROM (MINVALUE) TO (MAXVALUE);

Later, when I tried to split the partition with the format 'yyyy-MM-dd HH24' using TO_TIMESTAMP:

       ALTER TABLE MEASUREMENT DETACH PARTITION MEASUREMENT_P_INFINITY;
    
       CREATE TABLE MEASUREMENT_P_2020_11_24_03
         PARTITION OF MEASUREMENT
         FOR VALUES FROM (TO_TIMESTAMP('1970-01-01 10', 'yyyy-MM-dd HH24'))
         TO (TO_TIMESTAMP('2020-11-24 04', 'yyyy-MM-dd HH24'));
    
       ALTER TABLE MEASUREMENT ATTACH PARTITION MEASUREMENT_P_INFINITY
         FOR VALUES FROM (TO_TIMESTAMP('2020-11-24 04', 'yyyy-MM-dd HH24'))
         TO (MAXVALUE);

I received the follow error message:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "TO_TIMESTAMP"

I tried a simple query: select TO_TIMESTAMP('1970-01-01 10', 'yyyy-MM-dd HH24'); and it works just fine.

When I used postgres-13 it works OK.

PostgreSQL 11 partiotioning Documentation: https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

Answer 1

The ability to specify expressions for partition bounds was introduced in Postgres 12.

Allow partition bounds to be any expression (Kyotaro Horiguchi, Tom Lane, Amit Langote)

Such expressions are evaluated at partitioned-table creation time. Previously, only simple constants were allowed as partition bounds.

Note the "simple constants" part for earlier versions. You will have to upgrade to Postgres 12 or 13 if you do want to use that syntax.

In Postgres 11 you need to write the full timestamp value:

CREATE TABLE MEASUREMENT_P_2020_11_24_03
PARTITION OF MEASUREMENT
FOR VALUES FROM ('1970-01-01 10:00:00') TO ('2020-11-24 04:00:00');