PostgreSQL Table Partitioning

Recently found a table of PostgreSQL (collectively referred to as Test table) to 67G size, have to repartition, the following record the steps:

Preface, view data table structure (the table structure must be fictitious)

CREATE TABLE Test (  ID integer not NULL DEFAULT,  -- Login date  CONSTRAINT Test PRIMARY KEY (ID));

First, official start-create this Father table below the DW mode

CREATE TABLE dw.test (  ID integer not NULL DEFAULT,  -- login date, found, TMD is the character type of  CONSTRAINT test PRIMARY KEY (ID));

Second, create a sub-table, you will find a check, which is said to 2013-06 months of inheritance to the child table

--Creating a child table CREATE table dw.testl_yy13mm06 (CHECK (substring (logday from 1  for 7) ='2013-06') ) INHERITS (dw.test); CREATE TABLE dw.test_yy13mm07 (CHECK (substring (logday from 1  for 7) ='2013-07') ) INHERITS (dw.test);--Create an index, in fact, this index is built in the DW mode, delete the time need to execute: Drop index dw.idx_test_yy13mm06, I have eaten this loss, no DW mode create INDEX idx_test_yy13mm06 on Dw.test_yy13mm06 (Logday); CREATE INDEX idx_test_yy13mm07 on dw.test_yy13mm07 (Logday);

Iii. Creating triggers

CREATE OR REPLACE FUNCTION test_insert_trigger () RETURNS trigger as $ $BEGIN IF (substring (new.logday from 1  for 7) ='2013-06'Then INSERT into Dw.touch_ticket_channel_yy13mm06 VALUES (NEW.*); elsif (substring (new.logday from 1  for 7) ='2013-07'Then INSERT into Dw.touch_ticket_channel_yy13mm07 VALUES (NEW.*);     END IF; RETURN NULL; end;$ $LANGUAGE Plpgsql;

Iv. correlation Trigger, which executes the trigger before executing the inert statement

CREATE TRIGGER test_insert_triggerbefore Insert on dw.testfor each ROW EXECUTE PROCEDURE test_insert_trigger ();

V. Inserting test data

Insert INTO Dw.test (SELECT * from Test);

After execution, the test table below the public mode is migrated to the test table in DW mode as a whole.

Reference: http://www.php100.com/manual/PostgreSQL8/ddl-partitioning.html