Examples of transfer data to partitioned table in PostgreSQL

partitioned_table.sql

CREATE TABLE IF NOT EXISTS orders_with_partitions (
    id              uuid                        not null,
    processed_at    timestamp with time zone    not null,
    created_at      timestamp with time zone    not null default CURRENT_TIMESTAMP,
    data            jsonb                       not null
) PARTITION BY RANGE (created_at);

CREATE INDEX IF NOT EXISTS orders_with_partitions_created_at_idx
    ON orders_with_partitions (created_at);

CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m01 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
    
CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m02 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
    
CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m03 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
    
CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m04 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
    
CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m05 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

CREATE TABLE IF NOT EXISTS orders_with_partitions_y2024m06 PARTITION OF orders_with_partitions
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

transfer_data_to_partitioned_table.sql

CREATE OR REPLACE FUNCTION transfer_data(date_year text, date_month text, date_start timestamp, date_end timestamp) RETURNS INTEGER
AS $$
DECLARE
_row_count INTEGER := 0;
BEGIN
    RAISE NOTICE '%: INSERT INTO orders_with_partitions_y%m% SELECT ... created_at >= % AND created_at < %', now(), date_year, date_month, date_start, date_end;
    EXECUTE 'INSERT INTO orders_with_partitions_y' || date_year || 'm' || date_month || ' SELECT * FROM orders WHERE created_at >= ''' || date_start || ''' AND created_at<''' || date_end || '''';
    GET DIAGNOSTICS _row_count = ROW_COUNT;
    RETURN _row_count;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
_start_date timestamp := '2024-01-01'::timestamp;
r record;
_inserted_rows INTEGER := 0;
BEGIN
    FOR r IN SELECT dd::timestamp AS date_start,
         ((date_trunc('day', dd)::date) + INTERVAL '1 DAY') AS date_end,
         lpad(date_part('month', dd::timestamp)::text, 2, '0') AS date_month,
        date_part('year',dd::timestamp)::text AS date_year
        FROM generate_series (_start_date, (date_trunc('month', _start_date::date) + interval '1 month - 1 day')::timestamp, '1 day'::interval) dd
    LOOP
    _inserted_rows := transfer_data(r.date_year, r.date_month, r.date_start, r.date_end);
    COMMIT;
    IF _inserted_rows > 0 THEN
        RAISE NOTICE '%: Rows inserted: %', now(), _inserted_rows;
    ELSE
        RAISE NOTICE '%: No rows inserted', now();
    END IF;
        PERFORM pg_sleep(5);
    END LOOP;
END$$;

DROP FUNCTION transfer_data;