PostgreSQL Table Partitioning

I was just messing around with postgresql and table partitions and tablespaces. Here is a short SQL script that uses a virtual table that is partitioned by the modulus of the id column. Each partition lies on a different tablespace and in the schema partition.

begin;

drop table if exists partition.test_0;
drop table if exists partition.test_1;
drop table if exists test;

CREATE TABLE test (
	id SERIAL PRIMARY KEY
);

CREATE TABLE partition.test_0 (
	PRIMARY KEY (id),
	CHECK ( id % 2 = 0 )
)
INHERITS (test)
TABLESPACE ts1;

CREATE TABLE partition.test_1 (
	PRIMARY KEY (id),
	CHECK ( id % 2 = 1 )
)
INHERITS (test)
TABLESPACE ts2;

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.id % 2 = 0 ) THEN 
		INSERT INTO partition.test_0 VALUES (NEW.*); 
	ELSIF ( NEW.id % 2 = 1 ) THEN 
		INSERT INTO partition.test_1 VALUES (NEW.*);
	ELSE
		RAISE EXCEPTION 'This should not happen! See test_insert_trigger.';
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_test_mod_trigger 
	BEFORE INSERT ON test
	FOR EACH ROW EXECUTE PROCEDURE test_trigger_function();

commit;

Leave a Comment