I have created a partitioned table with 1 partition. below is the script for the same:
CREATE TABLE public.ms ( id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY, a int8 NOT NULL, b int4 NOT NULL, c varchar(2000) NULL, d int2 NOT NULL, e bool NOT NULL DEFAULT false, f timestamp NOT NULL DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP), g jsonb NULL, h int2 NOT NULL DEFAULT 1, i bool NOT NULL DEFAULT true, j int2 NULL, CONSTRAINT me_pkey PRIMARY KEY (id) )PARTITION BY RANGE (id) ; CREATE TABLE ms3_0 PARTITION OF public.msg FOR VALUES FROM (1) to (1000);
Now, when I'm running insert statement for this via below loop, I'm getting below error message:
do $$ begin for i in 1..1000 loop INSERT INTO public.ms (a, b, c, d, e, f, h, i , j) VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0); end loop; end;$$
SQL Error : ERROR: no partition of relation "ms" found for row Detail: Partition key of the failing row contains (id) = (1001).
However, till id '1000', partition has been created already. After that, I dropped the existing table and recreated the same table and partition again. Now, I started inserting records manually via a single 'insert' statement. Then, I could insert into them.
After, this, I have created 2nd partition like below:
CREATE TABLE ms3_1 PARTITION OF public.ms FOR VALUES FROM (1001) to (2000);
And, again executed the same loop for 1000 entries and got below error:
SQL Error : ERROR: no partition of relation "ms" found for row Detail: Partition key of the failing row contains (id) = (1000)
However, when I insert in the batch of 100 , like below: I could insert that:
do $$ begin for i in 0..100 loop INSERT INTO public.ms (a, b, c, d, e, f, h, i, j) VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0); end loop; end;$$
From the above whole scenario , I have a few queries:
- In the first case, why it wasn't inserting data (1000 values) into first partition when partition for first 1000 values was created already?
- After inserting 3-4 records into first partition and then After creating again another partition (ms3_1) with 1000 records, when I try to perform bulk insert of 1000 records into the same ms table, why I'm not able to make that?
Any help/reason for this will be appreciated here. Since, it will help me to configure partition as per my business requirement.