How to populate a table in PostgreSQL database using INSERT multiple rows approach?

In this blog I will demonstrate how to populate a table in PostgreSQL database using the INSERT multiple rows 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 approach I will use PostgreSQL anonymous code block:

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

      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 '1900-01-01 00:00:00' and '2024-01-01 00:00:00'
         random_timestamp := timestamp '1900-01-01 00:00:00' + random() * (timestamp '2024-01-01 00:00:00' - timestamp '1900-01-01 00:00:00');

         if idx = n_of_recs then

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

         else

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

         end if;

      end loop;

      raise notice 'query: %', query;

      execute query;

end$$;

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

postgres=# select count(1) from my_table;
 count
--------
 100000
(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: 151164.002 ms (02:31.164)
postgres=#

Conclusion.

In this blog I demonstrated how to populate a table in PostgreSQL database using INSERT multiple rows approach.

I considered PostgreSQL anonymous code block approach.

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