Iterate between dates and INSERT values in a performant way

Question

I have created a query that populates a datapoint with random values. The logic is simple: Iterate between the START and END dates and INSERT random values.

I want from this query to be very perfomant. For example populate every second of a year with values (what with this code will last ages). I am new to SQL statements of this complexity and I dont know the pitfalls and of it.

Are there some hidden areas in my code that can be improved? If I replace the random function with just a hardcoded value will it cause much boost? Is a loop with a lot of INSERT INTO time consuming; Is there a better way to Insert (some kind of batch insert)?

DO $$
DECLARE --Variables
    NODE_ID         bigint      :=  11;   -- The node id of the datapoint
    TIMESTAMP_START TIMESTAMP   := '2018-12-06 22:00:00';
    TIMESTAMP_END   TIMESTAMP   := '2018-12-10 00:00:00';
    TS_STEP         INTERVAL    := '30 minute'; 

    MAX_VALUE integer := 100;

BEGIN 
    LOOP     
        EXIT WHEN TIMESTAMP_START > TIMESTAMP_END;

        INSERT INTO datapoint_values (dp_id, ts, datatype, source, int_value, float_value)
        VALUES (NODE_ID, TIMESTAMP_START, 2, 0, floor(random()*(MAX_VALUE+1)), 0);

        TIMESTAMP_START := TIMESTAMP_START + TS_STEP;
    END LOOP;
END $$;

Answer 1

Instead of a loop which inserts one row after the other you should use generate_series to create all timestamps at once.

Untested:

DO $$
DECLARE --Variables
    NODE_ID         bigint      :=  11;   -- The node id of the datapoint
    TIMESTAMP_START TIMESTAMP   := '2018-12-06 22:00:00';
    TIMESTAMP_END   TIMESTAMP   := '2018-12-10 00:00:00';
    TS_STEP         INTERVAL    := '30 minute'; 

    MAX_VALUE integer := 100;

BEGIN 
    INSERT INTO datapoint_values (dp_id, ts, datatype, source, int_value, float_value)
    SELECT (NODE_ID, x, 2, 0, floor(random()*(MAX_VALUE+1)), 0)
    FROM generate_series(TIMESTAMP_START, TIMESTAMP_END, TS_STEP) t(x);
END $$;