Re: Partitioning: how to exclude unrelated partitions?

[paul rivers <rivers(dot)paul(at)gmail(dot)com>]

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