0

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


4
  • 1
    Why aren't you using declarative partitioning?
    – user1822
    Sep 18, 2022 at 16:21
  • 1
    @a_horse_with_no_name apparently because she wants the tables to have different columns.
    – jjanes
    Sep 19, 2022 at 0:35
  • If you want to use peculiar columns in the children, you will have to let them be set to their defaults, or somehow figure out the value to use from inside the trigger, or target INSERTs to the specific child rather than to the parent.
    – jjanes
    Sep 19, 2022 at 0:42
  • thanks @jjanes for the suggestion
    – Shiwangini
    Sep 24, 2022 at 8:18

1 Answer 1

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.

1
  • thanks for the response. I was just testing out the example ,if I can point/insert to multiple columns
    – Shiwangini
    Sep 24, 2022 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.