How to populate a table in PostgreSQL database using INSERT multiple rows and flash-by-chunks approach?

In this blog I will demonstrate how to populate a table in PostgreSQL database using the INSERT multiple rows by chunks approach.

Assume I have a table:

create table my_table(a varchar(100), b timestamp, c bigint);

postgres=# create table my_table(a varchar(100), b timestamp, c bigint);
CREATE TABLE
postgres=# \d my_table
                        Table "public.my_table"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 a      | character varying(100)      |           |          |
 b      | timestamp without time zone |           |          |
 c      | bigint                      |           |          |

postgres=#

To populate it using INSERT multiple rows and flash-by-chunks approach I will use PostgreSQL anonymous code block:

do $$
declare
   n_of_recs bigint := 1000000;
   chunk_size smallint := 1000;
   random_varchar_length smallint;
   random_varchar varchar(100);
   random_timestamp timestamp;
   random_int bigint;   
   query text; 
begin

      if chunk_size <= 0 then

         raise exception 'The chunk_size should be positive.';

      end if;

      query := 'insert into my_table values ';      

      for idx in 1..n_of_recs loop      

         -- some random varchar length between 1 and 100
         random_varchar_length := floor(random()*(100-1+1))+1;

         -- some random varchar
         random_varchar := array_to_string(array(select chr((ascii('a') + round(random() * 25)) :: integer) from generate_series(1,random_varchar_length)), ''); 

         -- some random int between 55 and 777777777
         random_int := floor(random()*(777777777-55+1))+55;

         -- some random timestamp between '1799-01-01 00:00:00' and '2080-01-01 00:00:00'
         random_timestamp := timestamp '1799-01-01 00:00:00' + random() * (timestamp '2080-01-01 00:00:00' - timestamp '1799-01-01 00:00:00');

         if (idx = n_of_recs) or (idx % chunk_size = 0) then

            query := query || format('(''%s'', ''%s'', %s);', random_varchar, random_timestamp, random_int);

            raise notice 'Flash. Populated Total: % recs', idx;

            execute query;

            query := 'insert into my_table values '; 

         else

            query := query || format('(''%s'', ''%s'', %s), ', random_varchar, random_timestamp, random_int);

         end if;

      end loop;     

end$$;

Now I will check the number of records inserted into the table my_table:

postgres=# select count(1) from my_table;
  count
---------
 1000000
(1 row)

postgres=#

To check how long does it take to populate the my_table with records I will use \timing execution time reporting option.

postgres=# \timing
Timing is on.

<run the PostgreSQL anonymous code block>

Time: 65128.156 ms (01:05.128)
postgres=#

Conclusion.

In this blog I demonstrated how to populate a table in PostgreSQL database using INSERT multiple rows and flash-by-chunks approach.

I considered PostgreSQL anonymous code block approach.

Also I've mentioned the \timing execution time reporting option in PostgreSQL.