peem-strike peem-strike
l1x
/ partition.sql
Last active
April 23, 2024 11:36
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; |
