-
-
Save lequocvuong/bded00f6a0ff93930a505a5de7ed1d6c to your computer and use it in GitHub Desktop.
Creating PostgreSQL table partitions automatically based on the date field (type date as well) -- each day is a single partition
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE testing_partition(patent_id BIGINT, date DATE) WITH ( OIDS=FALSE); | |
| CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS | |
| $BODY$ | |
| DECLARE | |
| partition_date TEXT; | |
| partition TEXT; | |
| BEGIN | |
| partition_date := to_char(NEW.date,'YYYY_MM_DD'); | |
| partition := TG_TABLE_NAME || '_' || partition_date; | |
| IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN | |
| RAISE NOTICE 'A partition has been created %',partition; | |
| EXECUTE 'CREATE TABLE ' || partition || ' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_TABLE_NAME || ');'; | |
| END IF; | |
| EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;'; | |
| RETURN NULL; | |
| END; | |
| $BODY$ | |
| LANGUAGE plpgsql VOLATILE | |
| COST 100; | |
| CREATE TRIGGER testing_partition_insert_trigger | |
| BEFORE INSERT ON testing_partition | |
| FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment