Question 217117 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Error on input data after creating postgres partition

Error on input data after creating postgres partition

SQL Error [23514]: ERROR: No partition for relation "data_part" for
that row Detail: Partition key value of failed row: (ymd) =
(20190902)

thank you!!

=== sql ===

CREATE TABLE data_part (
    ymd varchar(8) NOT NULL, 
    slot numeric(10) NOT NULL, 
    exst_dong_cd varchar(10) NOT NULL, 
    resia_cd varchar(10) NOT NULL, 
    odun5_aggp varchar(7) NOT NULL, 
    sexd varchar(7) NOT NULL, 
    popa numeric(10) NULL, 
    frst_reg_dt timestamp NULL, 
    last_mdfcn_dt timestamp NULL,
    inout_ctpv_cd varchar(7) NULL, 
    wknd_se_cd varchar(7) NULL 
) partition by range(ymd);


CREATE TABLE data_part_201909_1 PARTITION OF data_part
    FOR VALUES FROM ('20190901') TO ('20190902')
    TABLESPACE pg_default;
   
CREATE TABLE data_part_201909_2 PARTITION OF data_part
    FOR VALUES FROM ('20190903') TO ('20190904')
    TABLESPACE pg_default;   

CREATE TABLE data_part_201909_3 PARTITION OF data_part
    FOR VALUES FROM ('20190905') TO ('20190930')
    TABLESPACE pg_default;

I also tried the method below.

CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
);

CREATE TABLE measurement_y2020 (
    CHECK ( logdate >= DATE '2020-01-01' AND logdate <= DATE '2020-12-31' )
) INHERITS (measurement);
 
CREATE TABLE measurement_y2021 (
    CHECK ( logdate >= DATE '2021-01-01' AND logdate <= DATE '2021-12-31' )
) INHERITS (measurement);
 
CREATE TABLE measurement_y2022 (
    CHECK ( logdate >= DATE '2022-01-01' AND logdate < DATE '2022-12-31' )
) INHERITS (measurement);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    thank you., salomon.. got a hint

    from to range values ​​must overlap

    CREATE TABLE ggs_ppl_outflow_part_201909_1 PARTITION OF ggs_ppl_outflow_part
    FOR VALUES FROM ('20190901') TO ('20190903')
    TABLESPACE pg_default;
   
CREATE TABLE ggs_ppl_outflow_part_201909_2 PARTITION OF ggs_ppl_outflow_part
    FOR VALUES FROM ('20190903') TO ('20190905')
    TABLESPACE pg_default;   

CREATE TABLE ggs_ppl_outflow_part_201909_3 PARTITION OF ggs_ppl_outflow_part
    FOR VALUES FROM ('20190905') TO ('20190930')
    TABLESPACE pg_default;

  2. 0

    According to this postgres tutorial, when you do a RANGE PARTITION – minimum value is inclusive and maximum value is exclusive.

    Therefore, when you write the following code for example:

    CREATE TABLE data_part_201909_1 PARTITION OF data_part FOR VALUES FROM ('20190901') TO ('20190902') TABLESPACE pg_default;

    The value ‘20190902’ is not included in the range.

    So you probably need to change your code to be like:

    CREATE TABLE ggs_ppl_outflow_part_201909_1 PARTITION OF ggs_ppl_outflow_part
FOR VALUES FROM ('20190901') TO ('20190903')
TABLESPACE pg_default; 

CREATE TABLE ggs_ppl_outflow_part_201909_2 PARTITION OF ggs_ppl_outflow_part
FOR VALUES FROM ('20190903') TO ('20190905')
TABLESPACE pg_default;

CREATE TABLE ggs_ppl_outflow_part_201909_3 PARTITION OF ggs_ppl_outflow_part
FOR VALUES FROM ('20190905') TO ('20190930')
TABLESPACE pg_default;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search