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