Using Postgres FOR loop to generate data

I don't know about you but I often need a 100 of something in my database to properly test a page. If my UI isn't developed yet to handle that action or the count of something is too large, that's when I turn to SQL.

Today, for instance, I needed to test virtual scrolling and I didn't have enough data locally to trigger that behavior so I wrote a quick loop in Postgres to generate a 100 items:

DO $FN$
BEGIN
  FOR counter IN 1..100 LOOP
    RAISE NOTICE 'Counter: %', counter;

    EXECUTE $$ INSERT INTO items(name, active) VALUES ('Test item ' || $1, true) RETURNING id $$ 
      USING counter;
  END LOOP;
END;
$FN$

If you've followed me for a while, you'll recognize this loop from the wild SQL query I had to write for work. However, this loops is much simpler. And this item would generate 100 items in my database with sequentially numbered names.

Let's go through this query real quick

DO block

A DO block creates a special block that can execute some SQL. It's start and end is denoted by $FN$ in my query but you can use any delimiter that starts and ends with a dollar sign. So $DO$ works, as does $MY_FUNKY_LOOP$.

BEGIN...END

BEGIN and END create the section of the loop which runs the actual SQL statement. You can also have a DECLARE section which lets us assign and create some variables. We don't need it here.

FOR LOOP

The FOR counter IN 1..100 LOOP lets us create a for loop. Postgres has a handy way of creating ranges via the .. syntax (so 1..100 is 1 through 100). The current value of the range in the iteration is saved in the counter variable.

RAISE NOTICE

This is the console.log of Postgres :)

EXECUTE $$ $$ USING

EXECUTE will literally just execute the SQL between $$. What EXECUTE lets us do is specify USING which is used for variable substitution.

I like to think of this as a little self-executing function where USING lets us specify the parameters and execute runs the SQL and substitutes our parameter in the statement.

So as counter increases, the item name will increment as well.

Why even use this?

It's just a super handy small snippet for generating data. 🤷

What's cool is that the more I learn SQL, the more I find myself using these types of loops and logic and adding onto them. There's nothing stopping us from using some sort of random generator to create better varied data for development or for generating data in a staging environment.

Comments

[Evaldas Buinauskas]

How about this?

INSERT INTO items(name, active)
SELECT 'Test item ' || a.n, true
FROM generate_series(1, 100) as a(n)

SQL works much much better with set based operators than classic loops.

Obviously for 100 rows it won't make a noticeable difference 🙂