Thanks a ton Michael From: Michael Lewis <mle...@entrata.com> Sent: November-01-19 3:20 PM To: Shatamjeev Dewan <sde...@nbsps.com> Cc: pgsql-general <pgsql-gene...@postgresql.org> Subject: Re: Declarative Range Partitioning Postgres 11
On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan <sde...@nbsps.com<mailto:sde...@nbsps.com>> wrote: Hi Michael, I want to create a partition by year and subpartition by month in postgres 11 timestamp column. Please advise syntax. https://www.postgresql.org/docs/11/ddl-partitioning.html The documentation is rather clear with examples like- CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); Note- Don't partition on function results like date_part because performance will likely suffer greatly. Also note that the top end is always exclusive so the above give a continuous range for those two months. I would hesitate to partition by more than year alone before upgrading to PG v12. The speed improvements for more than 10-100 partitions (max recommended for PG11) is huge in 12.
