Want to reduce partition by moving to default partition present in table, for that created function which copies the data from partition id to default one but its failing with the error
<code> CREATE OR REPLACE FUNCTION move_and_drop_partitions(partition_ids integer[])
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
partition_id integer;
partition_name text;
begin
RAISE NOTICE 'Processing Started: %', partition_ids;
-- Loop through each partition ID
FOREACH partition_id IN ARRAY partition_ids
LOOP
-- Construct the partition name
partition_name := 'schedule_lines_' || partition_id;
RAISE NOTICE 'Processing partition: %', partition_name;
-- Move data from the current partition to the default partition
EXECUTE format('INSERT INTO schedule_lines_default SELECT * FROM %I', partition_name);
RAISE NOTICE 'Dropping Partition %', partition_ids;
-- Drop the current partition
EXECUTE format('DROP TABLE IF EXISTS %I', partition_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Rollback the transaction in case of an error
RAISE;
END;
$function$;
SELECT move_and_drop_partitions(ARRAY[109]);
</code>
<code> CREATE OR REPLACE FUNCTION move_and_drop_partitions(partition_ids integer[])
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
partition_id integer;
partition_name text;
begin
RAISE NOTICE 'Processing Started: %', partition_ids;
-- Loop through each partition ID
FOREACH partition_id IN ARRAY partition_ids
LOOP
-- Construct the partition name
partition_name := 'schedule_lines_' || partition_id;
RAISE NOTICE 'Processing partition: %', partition_name;
-- Move data from the current partition to the default partition
EXECUTE format('INSERT INTO schedule_lines_default SELECT * FROM %I', partition_name);
RAISE NOTICE 'Dropping Partition %', partition_ids;
-- Drop the current partition
EXECUTE format('DROP TABLE IF EXISTS %I', partition_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Rollback the transaction in case of an error
RAISE;
END;
$function$;
SELECT move_and_drop_partitions(ARRAY[109]);
</code>
CREATE OR REPLACE FUNCTION move_and_drop_partitions(partition_ids integer[])
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
partition_id integer;
partition_name text;
begin
RAISE NOTICE 'Processing Started: %', partition_ids;
-- Loop through each partition ID
FOREACH partition_id IN ARRAY partition_ids
LOOP
-- Construct the partition name
partition_name := 'schedule_lines_' || partition_id;
RAISE NOTICE 'Processing partition: %', partition_name;
-- Move data from the current partition to the default partition
EXECUTE format('INSERT INTO schedule_lines_default SELECT * FROM %I', partition_name);
RAISE NOTICE 'Dropping Partition %', partition_ids;
-- Drop the current partition
EXECUTE format('DROP TABLE IF EXISTS %I', partition_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Rollback the transaction in case of an error
RAISE;
END;
$function$;
SELECT move_and_drop_partitions(ARRAY[109]);
When using the function i am getting error as
SQL Error [23514]: ERROR: new row for relation
“schedule_lines_default” violates partition constraint¶