Search code examples
postgresqlpostgresql-12

Error When Creating an Unbounded Child Partition in PostgreSQL


I am running Postgres 12 and following examples from a book titled PostgreSQL: Up and Running published by O'Reilly Media. I am trying to create a partitioned table group like so:

CREATE TABLE logs (
    log_id int GENERATED BY DEFAULT AS IDENTITY,
    user_name varchar(50),
    description text,
    log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp
) PARTITION BY RANGE (log_ts);

The child partition is:

CREATE TABLE logs_gt_2011 PARTITION OF logs
FOR VALUES FROM ('2012-1-1') TO (unbounded);

This returns the error:

ERROR:  cannot use column reference in partition bound expression
LINE 2: for values from ('2012-1-1') to (unbounded); 

which makes it seem like it thinks unbounded is a column name. I thought unbounded was a Postgres keyword. Can you help me figure out the correct way to create this child partition?

I am running Postgres on a Windows 10 machine, from Powershell.


Solution

  • Not sure where the book gets it from, but according to the manual, that should be maxvalue:

    The special values MINVALUE and MAXVALUE may be used when creating a range partition to indicate that there is no lower or upper bound on the column's value

    CREATE TABLE logs_gt_2011 PARTITION OF logs
    FOR VALUES FROM ('2012-01-01') TO (maxvalue);