Generating a Series with PostgreSQL

I’ll preface this by saying that I don’t work with PostgreSQL too often, but this is one of the most useful functions I’ve seen.

There are many times working with data where we need to use a “Numbers” or a “Tally” table to generate a series of data to perform something in a set-based manner rather than using cursors or loops. These tables often assist with identifying gaps in data, counting character occurrences, etc.

PostgreSQL offers a slightly different solution to this problem. We no longer necessarily need a physical Tally table or even a user-defined table-valued function.

The generate_series function is one of the few currently available Set Returning Functions available in PostgreSQL. We can use this function to return a series of ints, bigints, timestamps, or numerics.

It uses the following syntax:
generate_series(start, stop, step interval)

If the step interval is a positive value (it’s default parameter uses a step size of one), no rows will be returned if the start value is greater than the stop value.

That’s enough explanation, let’s get into some examples.

SELECT *
FROM generate_series (1, 10);
SELECT *
FROM generate_series (1, 10, -1);
SELECT *
FROM generate_series (0.1, 1, 0.1);
SELECT *
FROM generate_series ('2020-03-31 00:00'::timestamp, '2020-04-09 00:00', '1 day');

I think you get the gist of how this works. I hope if you have a need for this in PostgreSQL you’ll find it useful! Jeff Moden has a great post titled The “Numbers” or “Tally” Table: What it is and how it replaces a loop available at SQLServerCentral if you’re interesting in learning more about the benefits of using a “Numbers” or “Tally” table. He also has a reference to his helpful “Splitter” function also available on in that post.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *