Hi I have a large table about 3B rows, that I would like to partition on a column called _received which is of type timestamp
I followed this https://medium.com/@StreamBright/creating-partitions-automatically-in-postgresql-7006d68c0fbb and came up with CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS $BODY$ DECLARE partition_date TEXT; partition TEXT; BEGIN partition_date := to_char(NEW._received,'YYYYMM'); 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 ( to_char(_received, '''YYYYMM''') = ''' || partition_date || ''')) INHERITS (' || TG_TABLE_NAME || ');'; END IF; EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').* RETURNING patent_id;'; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; Which I believe generates something like this CREATE TABLE feedintra_201707( check (to_char(_received,'YYYYMM') = '201707' )) INHERITS (XXXXXX); My concern is the cost of doing to_char on every row. So Is this going to work Is this the right way to do it. I was thinking if I could change the check to be something like check ( _recieved >= YYYYMM 1 00:00 and _recieved <= YYYYMM <last day of the month> 23:59:59.999 ) so I am not sure how to make up the above line based on a timestamp Thanks Alex
