我想做一个日常分区。我知道oracle是这样的。
CREATE TABLE "PUBLIC"."TEST"
(
"ID" NUMBER(38,0) NOT NULL ENABLE,
"SOME_FIELD" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"ANOTHER_FIELD" VARCHAR2(36 BYTE) NOT NULL ENABLE,
TABLESPACE "PUBLIC"."TEST_DATA"
PARTITION BY RANGE ("TEST_DATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "TEST_P1"
VALUES LESS THAN (TIMESTAMP' 2019-01-01 00:00:00') TABLESPACE "TEST_DATA" );
PostgreSQL 怎么样?
新编辑:
示例脚本:
该脚本将在一个表中维护前 15 天的数据,即“p1”,而剩余天数的数据则在另一个分区中。
1-创建自动分区取决于插入命令的日期范围
2-在脚本中我还提到了如何在所需列上添加索引。
3- 日期范围从 1 日到 14 日的数据将添加到分区“p1”中,其余数据将添加到分区“p2”中。
示例脚本:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
partition_day int;
startdate date;
enddate date;
BEGIN
partition_day := to_char(NEW.logdate,'DD');
partition_date := to_char(NEW.logdate,'YYYY_MM');
IF partition_day < 15 THEN
partition := TG_RELNAME || '_' || partition_date || '_p1';
startdate := to_char(NEW.logdate,'YYYY-MM-01');
enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
ELSE
partition := TG_RELNAME || '_' || partition_date || '_p2';
startdate := to_char(NEW.logdate,'YYYY-MM-15');
enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
END IF;
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 ( logdate >= DATE ''' || startdate || ''' AND logdate <= DATE ''' || enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
EXECUTE 'CREATE INDEX ' || partition || '_logdate ON ' || partition || '(logdate)';
EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER testing_partition_insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE new_partition_creator();
postgres=# insert into measurement values(1,'2017-10-11',10,10);
NOTICE: A partition has been created measurement_2017_10_p1
INSERT 0 0