In this blog I will demonstrate how to populate a table in PostgreSQL database using the INSERT multiple rows by chunks 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 and flash-by-chunks approach I will use PostgreSQL anonymous code block:
do $$
declare
n_of_recs bigint := 1000000;
chunk_size smallint := 1000;
random_varchar_length smallint;
random_varchar varchar(100);
random_timestamp timestamp;
random_int bigint;
query text;
begin
if chunk_size <= 0 then
raise exception 'The chunk_size should be positive.';
end if;
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 '1799-01-01 00:00:00' and '2080-01-01 00:00:00'
random_timestamp := timestamp '1799-01-01 00:00:00' + random() * (timestamp '2080-01-01 00:00:00' - timestamp '1799-01-01 00:00:00');
if (idx = n_of_recs) or (idx % chunk_size = 0) then
query := query || format('(''%s'', ''%s'', %s);', random_varchar, random_timestamp, random_int);
raise notice 'Flash. Populated Total: % recs', idx;
execute query;
query := 'insert into my_table values ';
else
query := query || format('(''%s'', ''%s'', %s), ', random_varchar, random_timestamp, random_int);
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>
Time: 65128.156 ms (01:05.128)
postgres=#
Conclusion.
In this blog I demonstrated how to populate a table in PostgreSQL database using INSERT multiple rows and flash-by-chunks approach.
I considered PostgreSQL anonymous code block approach.
Also I've mentioned the \timing execution time reporting option in PostgreSQL.
Top comments (0)