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 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.
Top comments (0)