Populating a PostgreSQL table with a random data: A step-by-step guide
Tags: postgres, database, devops
Published: 2022-12-24 12:25:35


In this blog I will demonstrate how to create a table and populate it with a random data.

`Step #1` - Create a table

For example, I will create a table my_table that has 4 columns. 

- column id is auto-generated sequence
- column a is a string of maximum length 100
- column b is a timestamp 
- column c is a small integer

```
create table if not exists my_table(id serial, a varchar(100), b timestamp, c smallint);

postgres=# create table if not exists my_table(id serial, a varchar(100), b timestamp, c smallint);
CREATE TABLE                    
postgres=# \d my_table
                                      Table "public.my_table"
 Column |            Type             | Collation | Nullable |               Default
--------+-----------------------------+-----------+----------+--------------------------------------
 id     | integer                     |           | not null | nextval('my_table_id_seq'::regclass)
 a      | character varying(100)      |           |          |
 b      | timestamp without time zone |           |          |
 c      | smallint                    |           |          |

postgres=#
```

`Step #2` - Create a PostgreSQL block, that will implement insert of <n_of_recs> records into the my_table table

```
do $$
declare
   n_of_recs smallint := 20000;
   random_varchar_length smallint;
   random_varchar varchar(100);
   random_timestamp timestamp;
   random_int smallint;   
   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 25 and 10000
   	random_int := floor(random()*(10000-25+1))+25;

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

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

   	execute query using random_varchar, random_timestamp, random_int;

   end loop;

   -- check the number of records in the table my_table
   query := 'select count(1) n from my_table';

   for rec in execute query loop
   	raise notice 'Num of recs in the table my_table: %', rec.n;
   end loop;

end; $$
```

`Step #3` (optional) - Create a function based on the PostgreSQL block above

```
create or replace function insert_records_into_the_my_table(n_of_recs smallint default 20000) 
 returns text 
as 
$$
declare
   random_varchar_length smallint;
   random_varchar varchar(100);
   random_timestamp timestamp;
   random_int smallint;   
   query text;
   rec record;
   outputTxt text;
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 25 and 10000
   	random_int := floor(random()*(10000-25+1))+25;

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

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

   	execute query using random_varchar, random_timestamp, random_int;

   end loop;

   -- check the number of records in the table my_table
   select 'Num of recs in the table my_table: ' || count(1) into outputTxt from my_table;
   return outputTxt;

end;
$$ 
language plpgsql;


postgres=# \df
                                           List of functions
 Schema |               Name               | Result data type |       Argument data types        | Type
--------+----------------------------------+------------------+----------------------------------+------
 public | insert_records_into_the_my_table | text             | n_of_recs smallint DEFAULT 20000 | func
(1 row)

postgres=#
```

Let's examine the newly created function:

```
postgres=# select insert_records_into_the_my_table(234::smallint);
    insert_records_into_the_my_table
----------------------------------------
 Num of recs in the table my_table: 234
(1 row)

postgres=#


postgres=# select insert_records_into_the_my_table();
     insert_records_into_the_my_table
------------------------------------------
 Num of recs in the table my_table: 20234
(1 row)

postgres=#
```

Let's see the actual records generated:

```
 select * from my_table limit 10;

  id   |                                                  a                                                  |             b              |  c
-------+-----------------------------------------------------------------------------------------------------+----------------------------+------
 20835 | lysurmsbdfwrrasnnlmzujwdtuw                                                                         | 1978-09-05 01:45:25.148229 | 8642
 20836 | spfgyjksbxxsbfyixbhygzqizeiisldfcjssvwdaxdpkrwjsmhwqvozfkcipissbgi                                  | 1915-06-16 12:08:08.978399 | 6590
 20837 | kxibatprlkckkotcckqfmmroflbhvakoxipuqwjmc                                                           | 1992-10-11 08:50:10.587357 |  606
 20838 | ifjekeirgkodhqakselytruphlxsnnlblwdyjkhrjxibpftiexqrsdtomjhuuntozifcwahdsrekhfbaecwbvow             | 1995-06-09 15:44:00.125167 | 8600
 20839 | duwdzmvzwkcygyjqokctszlffcnpclojzvweseaibvemghlgentemvboyhszzlmdmkfrugkxkdkowoyeyogercckpygfuukluth | 2009-06-22 07:26:56.044023 | 3569
 20840 | qvuvlftidrwitheqywzvcvhvmvmkxelkvxyufjfdkvybeyajnmoldqkuwxioyoiykeoibdvnbdbyyaufdqpjlrdgbbkvsy      | 1900-11-18 17:56:54.171598 | 8399
 20841 | bpxdytnu                                                                                            | 1981-11-06 08:09:35.857618 | 9508
 20842 | phwsewlpmerayuovgakjtbzflggeqqqxsqetxufuoe                                                          | 1933-12-02 02:50:20.117185 | 5099
 20843 | nwxjdoksxwsvkjmpfyvayvwqwckyeyqxrlagn                                                               | 1999-11-12 01:56:10.847324 | 7316
 20844 | ksjvvwpisjfpsapwccvdpcfgchjyrhwqof                                                                  | 1958-08-06 07:56:47.420669 | 5673
(10 rows)
```

`Conclusion`

In this blog I've demonstrated how to create a table and populate it with a random data. I've considered PostgreSQL block approach and also PostgreSQL function implementation.