postgresql 分表分区分库 测试

创建子表
CREATE TABLE testdb_2022 (
    CHECK (  createtime >= DATE '2022-01-01' AND  createtime < DATE '2023-01-01' )
) INHERITS (testdb);
CREATE TABLE testdb_2023 (
    CHECK (  createtime >= DATE '2023-01-01' AND  createtime < DATE '2024-01-01' )
) INHERITS (testdb);




分区函数
CREATE OR REPLACE FUNCTION function_insert_testdb()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.vdate >= DATE '2022-01-01' AND  NEW.vdate < DATE '2023-01-01' ) THEN

        INSERT INTO testdb_2022 VALUES (NEW.*);

    ELSIF ( NEW.vdate >= DATE '2023-01-01' AND  NEW.vdate < DATE '2024-01-01' ) THEN

        INSERT INTO testdb_2023 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the function_insert_tb_log() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;


触发器

CREATE TRIGGER trigger_insert_testdb
    BEFORE INSERT ON testdb
    FOR EACH ROW
    EXECUTE PROCEDURE function_insert_testdb();