Why can a partition bound expression not include a casting operation?

All we need is an easy explanation of the problem, so here it is.

In the Postgres CREATE TABLE docs, creating a partition of a table is described like this:

PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }

partition_bound_expr is any variable-free expression (subqueries, window functions, aggregate functions, and set-returning functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated once at table creation time…

So why is this a syntax error?

CREATE TEMPORARY TABLE foo
(ts timestamptz)
PARTITION BY RANGE(ts)
;

CREATE TEMPORARY TABLE foo_1970_01_01
PARTITION OF foo
FOR VALUES
  FROM ('1970-01-01 00:00+00:00'::timestamptz)
  TO ('1970-02-01 00:00+00:00'::timestamptz)
;

The error is:

ERROR:  syntax error at or near "::"
LINE n: FROM ('1970-01-01 00:00+00:00'::timestamptz)
                                      ^

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You must be using PostgreSQL v11 or older.

Back then, it could only be a literal:

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

See the documentation.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply