Re: Partition - Mailing list pgsql-general

Hi, I have read and re-read the Partitioning chapter (http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I still don't see how to implement this use case:

One table storing current data, let's call it the "master table", then one or more partitions with old data.

For example, the master table is this:

    create table log(
      idlog serial not null,
      date_time timestamp not null default now(),
      log varchar(255),
      primary key(idlog)
    );

The documentation says the master table should be empty, then a trigger must evaluate a condition, the date_time field for example, and insert the data in the corresponding table. This is a *rare* condition, because in the log example, new content is created without knowing its date and time in advance. For example:

    insert into log(log) values('log this please.');

The date_time column will set the now() value.

Now, by following the example, to create a child table I'll do

    create table log_old( ) inherits (log);

This raises the 1nst question, how can I define a *dynamic* check,
for checking older than X days?. Is this possible?.

An idea (didn't test):

    check (date_time::date < now()::date - '30 day'::interval)

Then, the trigger, after each insert should *move* old data to log_old.

The only problem I see here is the master table isn't empty, but contains current data. The question is, will it work as expected?, I mean when I do "select * from log" I'll get an *union* of new and old data?.