Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Use/ "inserted partition key does not map to any table partition" Is Reported When Data Is Inserted into a Partitioned Table
Updated on 2023-04-18 GMT+08:00
View PDF
Share

"inserted partition key does not map to any table partition" Is Reported When Data Is Inserted into a Partitioned Table

Symptom

"inserted partition key does not map to any table partition" is reported when data is inserted into a partitioned table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TABLE startend_pt (c1 INT, c2 INT) 
DISTRIBUTE BY HASH (c1) 
PARTITION BY RANGE (c2) (
    PARTITION p1 START(1) END(1000) EVERY(200) ,
    PARTITION p2 END(2000),
    PARTITION p3 START(2000) END(2500) ,
    PARTITION p4 START(2500),
    PARTITION p5 START(3000) END(5000) EVERY(1000) 
);
SELECT partition_name,high_value FROM dba_tab_partitions WHERE table_name='startend_pt';
 partition_name | high_value
----------------+------------
 p1_0           | 1
 p1_1           | 201
 p1_2           | 401
 p1_3           | 601
 p1_4           | 801
 p1_5           | 1000
 p2             | 2000
 p3             | 2500
 p4             | 3000
 p5_1           | 4000
 p5_2           | 5000
(11 rows)

INSERT INTO startend_pt VALUES (1,5001);
ERROR:  dn_6003_6004: inserted partition key does not map to any table partition

Possible Causes

In range partitioning, the table is partitioned into ranges defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. Data is mapped to a created partition based on the partition key value. If the data can be mapped to, it is inserted into the specific partition; if it cannot be mapped to, error messages are returned.

In this example, partition_key of the partitioned table tpcds.startend_pt is c2. Data inserted into the table is divided into five partitions that do not overlap. Data 5001 corresponding to column c2 exceeds the range (5001>5000). As a result, an error is reported.

Handling Procedure

Plan partitions properly to ensure that the data can be inserted as planned.

If the planned partitions cannot meet the actual requirements, you can add partitions and then insert data. For the preceding case, you can add partition c2. The partition range is between 5000 and MAXVALUE.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
ALTER TABLE startend_pt ADD PARTITION P6 VALUES LESS THAN (MAXVALUE);
SELECT partition_name,high_value FROM dba_tab_partitions WHERE table_name='startend_pt';
 partition_name | high_value
----------------+------------
 p1_0           | 1
 p1_1           | 201
 p1_2           | 401
 p1_3           | 601
 p1_4           | 801
 p1_5           | 1000
 p2             | 2000
 p3             | 2500
 p4             | 3000
 p5_1           | 4000
 p5_2           | 5000
 p6             | MAXVALUE
(12 rows)

INSERT INTO startend_pt VALUES (1,5001);
SELECT * FROM startend_pt;
 c1 |  c2
----+------
  1 | 5001
(1 row)
Parent topic: Database Use