是否可以只在分区中插入一行

Question

我有一个已经存在的表，我想对它进行分割。这是我创建分区的脚本：

create or replace function create_partition_and_insert_to_partition_bundle() returns trigger as
$$
declare
partition text;
dt_constraint text;
begin
dt_constraint := format( 'y%sq%s', (select date_part('year', date(new.created))), select ceil(date_part('month', date(new.created))::float / 3));
partition := format( 'bundle_%s', dt_constraint);
if not exists(select relname from pg_class where relname = partition) then
execute 'create table '||partition||' (like bundle including all) inherits (bundle)';
end if;
execute 'insert into ' || partition || ' values ( ($1).* )' using new;
return null;
end
$$ language plpgsql;
create trigger create_insert_partition_bundle before insert on bundle for each row execute procedure create_partition_and_insert_to_partition_bundle();
set constraint_exclusion = partition;

当我添加新行时，触发器会运行并为一个新的quart创建一个新分区，但该行也会插入到父表bundle中。是否可以只插入到分区中？我应该如何更改脚本？

Answer 1

我个人觉得继承很难理解。所以我使用分区。以下使用分区。你甚至不需要扳机。

CREATE TABLE  bundle (
bundle_id   int not null,
created date not null,
misc    text
) PARTITION BY RANGE (created);
CREATE TABLE bundle_y2022q1 PARTITION OF bundle
FOR VALUES FROM ('2022-01-01') TO ('2022-04-01');
CREATE TABLE bundle_y2022q2 PARTITION OF bundle
FOR VALUES FROM ('2022-04-01') TO ('2022-07-01');
CREATE TABLE bundle_y2022q3 PARTITION OF bundle
FOR VALUES FROM ('2022-07-01') TO ('2022-10-01');    
CREATE TABLE bundle_y2022q4 PARTITION OF bundle
FOR VALUES FROM ('2022-10-01') TO ('2023-01-01');