On 03/18/2016 02:55 AM, Leonardo M. Ramé wrote:
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.
True but you can catch that value in the BEFORE trigger as
NEW.date_time. A default is still just a value being entered into the field.
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)
Where are you putting this CHECK?
FYI, should not need the casts to date. Interval will work with datetimes.
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?.
If you use a BEFORE trigger on the master table and redirect the INSERT
to a partition and RETURN NULL from said trigger, then the INSERT will
not happen on the master.
Regards,
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
