Craft up practice data sets with the PostgreSQL generate_series() function.

Published in

codeburst

5 min readMar 4, 2018

I’ve come to realize, I absorb and learn the best by doing. Oftentimes, I need a fake or mock data set to work with as I study and learn SQL.

Normally I would create this data by hand. However, after watching an interesting video (link provided below), I decided to apply some of the examples I found there, for my own use case.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and PostgreSQL 10.3 for these exercises.

Quick Introduction…

I happened upon this great YouTube video by PG Casts and thought to apply what I learned there, into a blog post. Along with actually using the information, in mock data sets I am constantly creating as I learn SQL database programming.

This blog post will cover an example use of the generate_series() function. We can utilize it to create a mock data set for study, learning, and practice.

Brief Demo.

Let’s get an idea of what the generate_series() function can do.
Here are a some simple examples.

We can generate 8 consecutive numbers:

fab_tracking=> SELECT generate_series(1, 8) AS num;
 num 
 — — -
 1
 2
 3
 4
 5
 6
 7
 8
(8 rows)

Placing in a step determines the amount to be advanced. Here we are incriminating by 2:

fab_tracking=> SELECT generate_series(1, 8, 2) AS num;
 num 
 — — -
 1
 3
 5
 7
(4 rows)

String concatenation works as well. In this statement, we are appending the numbers returned from the function to the string ‘My Number-’:

fab_tracking=> SELECT ‘My Number-’ ||generate_series(1, 8, 2) AS num;
 num 
 — — — — — — -
 My Number-1
 My Number-3
 My Number-5
 My Number-7
(4 rows)

Let’s apply what we have learned so far, to our specific use case.
To change things up, we’ll provide a bit of randomness for apipe_name column, by using a CASE statement and the random() function.

This way, we can provide a list of choices and change up the naming.

fab_tracking=> SELECT (CASE (random() * 2)::INTEGER
 WHEN 0 THEN ‘Pipe-’
 WHEN 1 THEN ‘Joint-’
 WHEN 2 THEN ‘Asset-’
END) || pipe_num
 FROM generate_series(1, 6) AS pipe_num;
 ?column? 
 — — — — — 
 Pipe-1
 Pipe-2
 Joint-3
 Joint-4
 Asset-5
 Joint-6
(6 rows)

This query uses a number generated by the random() function, and depending on it, chooses one of the applicable names from the 3 WHEN/THEN statements.
E.g., If the ‘random number’ is 1, ‘Joint-’ is used for the name etc…

Let’s include the step parameter in the function call this time, to make various pipe lengths (Note — I limit the output to just 5 rows for brevity):

fab_tracking=> SELECT generate_series(40.3, 79.4, 1.22) AS pipe_length
LIMIT 5;
 pipe_length 
 — — — — — — -
 40.3
 41.52
 42.74
 43.96
 45.18
(5 rows)

Here, 1.22 is the amount to increment or advance the size series by.

Cloning Practice Tables

To start, we’ll get a description of the table we want to clone:

Then using CREATE TABLE AS syntax, we make a copy of the table, with no data:

fab_tracking=> CREATE TABLE staging_two
AS TABLE asset_staging
WITH NO DATA;
CREATE TABLE AS

A description of the cloned staging_two table:

Now that the practice table in place, we can populate it with our mock data, using generate_series().

The INSERT statement below, uses a SELECT statement, composed of various generate_series() function calls to populate table staging_two

INSERT statement using SELECT and generate_series()

We can see the return tag of INSERT 0 39 but let’s verify with a SELECT statement as well:

fab_tracking=> SELECT COUNT(*) FROM staging_two;
 count 
 — — — -
 39
(1 row)

I will SELECT the pipe_name and pipe_length columns for rows 11–15:

fab_tracking=> SELECT pipe_name, pipe_length
 FROM staging_two
 OFFSET 10
 LIMIT 5;
 pipe_name  | pipe_length 
 — — — — — -+ — — — — — — -
 Pipe-11    | 49.80
 Pipe-12    | 50.84
 Pipe-13    | 51.88
 Pipe-14    | 52.92
 Pipe-15    | 53.96
(5 rows)

There is our generated data for our table.

We can use this table now for fully functional query’s and working practice.

Discover More.

The generate_series() function has a plethora of potential uses for working with data sets. This is but one example of its use.
I hope you find this blog post useful for ideas how you can use it and I would love to hear those ideas you come up with as well.
As always, visit the impeccable On-line PostgreSQL Documentation for further study and information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit my Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from my blog, “Digital Owl’s Prose”, via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! Be sure and visit the "Best Of” page for a collection of my best blog posts while you are there!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.