How to populate a table with one million records in PostgreSQL database?

In this blog I will demonstrate how to populate a table with one million records in PostgreSQL database.

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 with one million records I will use PostgreSQL anonymous code block:

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

   for idx_rec 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');

      query := 'insert into my_table values($1, $2, $3)';

      execute query using random_varchar, random_timestamp, random_int;

      if idx_rec % 100000 = 0 then
         raise notice 'Num of recs inserted into the table my_table: %', idx_rec;
      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>

NOTICE:  Num of recs inserted into the table my_table: 100000
NOTICE:  Num of recs inserted into the table my_table: 200000
NOTICE:  Num of recs inserted into the table my_table: 300000
NOTICE:  Num of recs inserted into the table my_table: 400000
NOTICE:  Num of recs inserted into the table my_table: 500000
NOTICE:  Num of recs inserted into the table my_table: 600000
NOTICE:  Num of recs inserted into the table my_table: 700000
NOTICE:  Num of recs inserted into the table my_table: 800000
NOTICE:  Num of recs inserted into the table my_table: 900000
NOTICE:  Num of recs inserted into the table my_table: 1000000
DO
Time: 79357.960 ms (01:19.358)
postgres=#

Conclusion.

In this blog I demonstrated how to populate a table with one million records in PostgreSQL database.

I considered PostgreSQL anonymous code block approach.

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