Re: Partitioning: how to exclude unrelated partitions?

Sean Z. wrote:
> Hi,
>
> I partitioned a table "events" into 31 tables, based on "day" of
> event_time.
>
> I did 3 steps to setup partition, after creating partition tables:
>
> 1. Add the constraint to the 31 partition tables like:
>
> ALTER TABLE events_day_1
> ADD CONSTRAINT events_day_1_event_time_check CHECK
> (date_part('day'::text, event_time) = 1::double precision);
>
> [snip]
>
> Do I miss anything?
>
> Best,
> Sean
>

I believe you can only partition on literal values. You'll probably
need to include a derived 'day' column in your table that you can
populate in the rule. Your query will then need to include the literal
day value in the where clause, rather than the event_time.

Check out the caveats section for partitioning here (bottom of page, 5.9.5):

http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html

It's not terribly obvious at first reading, as the focus is more on
querying than designing the table. Maybe that would be worth expanding
on a little in the docs?

Regards,
Paul