one partition for all old data

[megadawn]

Hi,
Is there any way to automatically insert some data into one partition (table_old_data) if the value less than something?
Like this in postgres:
CREATE OR REPLACE FUNCTION table_insert_trigger()
.......
BEGIN
IF (NEW.time_key >= TIMESTAMP '2016-10-01 00:00:00' and NEW.time_key < TIMESTAMP '2016-10-02 00:00:00') THEN
INSERT into table_pt_01 values (NEW.) ;
.......................................................
ELSE
INSERT into table_old values (NEW.) ;
END IF;
RETURN NULL;
END;

thanks

[zilder]

Hi,

You cannot create a partition with 'less than' constraint with pg_pathman at the moment. But you can create a large enough partition which would cover all the possible values in the past that could be inserted. For example let's say you have a table partitioned by timestamp field dt:

create table table_a(dt timestamp not null);
select create_range_partitions('table_a', 'dt', '2016-10-01'::date, '1 day'::interval, 3);

Then create a new table which has an exact same ddl-schema (see an example) and attach it to the partitioned one by performing attach_range_partition() function with a large enough range specified:

create table table_a_archive (like table_a including all);
select attach_range_partition('table_a', 'table_archive', '2000-01-01', '2016-10-01');

Now depending on the key value rows would be automatically distributed to partitions:

insert into table_a values ('2016-10-02');
insert into table_a values ('2015-01-01');

-- values less than '2016-10-01' are in table_a_archive partition
explain select * from table_a where dt < '2016-10-01';
                               QUERY PLAN
-------------------------------------------------------------------------
 Append  (cost=0.00..32.60 rows=2260 width=8)
   ->  Seq Scan on table_a_archive  (cost=0.00..32.60 rows=2260 width=8)
(2 rows)

select * from table_a where dt < '2016-10-01';
         dt
---------------------
 2015-01-01 00:00:00
(1 row)

-- values more or equal than '2016-10-01' are in a corresponding partition
explain select * from table_a where dt >= '2016-10-01';
                            QUERY PLAN
-------------------------------------------------------------------
 Append  (cost=0.00..97.80 rows=6780 width=8)
   ->  Seq Scan on table_a_1  (cost=0.00..32.60 rows=2260 width=8)
   ->  Seq Scan on table_a_2  (cost=0.00..32.60 rows=2260 width=8)
   ->  Seq Scan on table_a_3  (cost=0.00..32.60 rows=2260 width=8)
(4 rows)

select * from table_a where dt >= '2016-10-01';
         dt
---------------------
 2016-10-02 00:00:00
(1 row)

[megadawn]

just tried, all worked!
Thank you problem solved