partitioning with inheritance

Question

Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html#:~:text=return%20an%20error.-,5.11.3.%C2%[…]20Using%20Inheritance,-While%20the%20built

I was trying partitioning with inheritance, but after adding extra column in one of child table when I tried to insert data directly in parent table with that range - i get below message:

 insert into measurement values (2, '2006-02-02',4,5,'channa');
ERROR:  INSERT has more expressions than target columns
LINE 1: ...sert into measurement values (2, '2006-02-02',4,5,'channa');

However, when I insert directly - i can insert:

insert into measurement_y2006m02 values (2, '2006-02-02',4,5,'channa');
INSERT 0 1

Isn’t possible to insert directly with parent table name - when we’re inserting with right criteria. attaching my script for the reference.

-- partitioning with table inheritance



create table measurement 
(city_id int not null,
logdate date not null,
peaktemp int,
unitsales int);





-- adding partitions

create table measurement_y2006m02(
check (logdate >= date '2006-02-01' and logdate < date '2006-03-01'))
inherits (measurement);

create table measurement_y2006m03(
check (logdate >= date '2006-03-01' and logdate < date '2006-04-01'))
inherits (measurement);


create table measurement_y2007m11(
check (logdate >= date '2007-11-01' and logdate < date '2007-12-01'))
inherits (measurement);


create table measurement_y2007m12(
check (logdate >= date '2007-12-01' and logdate < date '2008-01-01'))
inherits (measurement);


create table measurement_y2008m01(
check (logdate >= date '2008-01-01' and logdate < date '2008-02-01'))
inherits (measurement);



-- create index on the key column of child table

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);




-- adding trigger

create trigger insert_measurement_trigger
    before insert on measurement
    for each row execute function measurement_insert_trigger();


-- creating function

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$

LANGUAGE plpgsql;



--  adding extra column in one of the partition 

alter table measurement_y2006m02 add column test varchar ;

-- insert into table

 insert into measurement values (2, '2006-02-02',4,5,'channa');
ERROR:  INSERT has more expressions than target columns
LINE 1: ...sert into measurement values (2, '2006-02-02',4,5,'channa');


-- inserting direct
insert into measurement_y2006m02 values (2, '2006-02-02',4,5,'channa');
INSERT 0 1

Answer 1

I wouldn't call what you are doing "partitioning": in a partitioned table, all partitions have the same columns. PostgreSQL complains that you are trying to insert a value into a column of measurement that doesn't exist in that table, and the only solution is to directly insert into the table that has that column.

It is hard to suggest the best solution for your problem unless you tell us what you are trying to achieve here.