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?.
Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877