Is it possible to insert a row only to a partition?

Максим Рыбалкин 0
Viewed 30

I have an already exist table which I want to be divided. Here is my script for partitions creating:

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;

When I add a new row, the trigger runs and creates a new partition for a new quart, but the row is also inserted to a parent table bundle. Is it possible to insert only to a partition, and how should I change my script?

database-partitioning sql postgresql
1 Answers

Personally i found inheritance very hard to understand. So I use partition. The following is using partition.You don't even need trigger.

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');
userBct310
Related