Postgres: Insert Multiple Rows (Like A BUNCH!)

**************

Disclaimer!

Regular INSERT is not what I am interested in here!

**************

When I created a database and some APIs, I always want a bunch (like hundreds) of dummy data to test things out!

Get Itsuki’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

In this article, I will be sharing with you how we can insert such rows in one shot!

Table with Serial Key

CREATE TABLE demo_table (
    id serial primary key,
    age int default floor(random() * 10 + 100)::int,
    name text default ('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]
);

INSERT INTO demo_table SELECT generate_series(1,100)

Table Without Serial Key

I will be using uuid here as an example.

CREATE TABLE demo_table (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    age int default floor(random() * 10 + 100)::int,
    name text default ('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]
);

INSERT INTO demo_table SELECT from generate_series(1,100)

Non-Default Values

What if our columns do not have default values or what if we do not want to use those?

We can also specify values like following.

INSERT INTO demo_table (id, age, "name")
select gen_random_uuid(), 1000000, 'itsuki'
FROM 
generate_series(1,100)

As you can see, if we want a different data for each row, all we have to do is to define a function that generates a random value and use it in SELECT.

Thank you for reading!

That’s it for this short article!

Hope that it can speed up your development (may be just a little bit)!

Happy inserting!