Generating integers for a SQL database

by Mike Sherrill

In PostgreSQL, it’s dead simple to generate a set of integers on the fly. This SQL statement generates a set of integers from 1 to 10.

select generate_series(1, 10);

The generate_series() function returns a set; you can use it as if it were a table. But often you’re better off with a base table of integers. Now, in PostgreSQL, you can populate a table of integers using generate_series().

create table integers (
  n integer primary key
);

insert into integers (n) 
select generate_series(1, 10);

But what do you do if you’re not using PostgreSQL? There are several ways you can attack this problem.

Skip to my preferred alternative.

Install PostgreSQL

The first alternative is to install PostgreSQL, and use it instead of your current dbms. This has a certain appeal to me, but I admit it might not be practical or economical. PostgreSQL itself is free, but when your goal is just to have a table of integers, installing a new dbms is a heavy burden. Also, we all know that sensible solutions like this are often against a company’s policies.

Export from a shared host

Many developers have their own web sites, hosted on shared web hosts. And many of these shared hosts support PostgreSQL. So you might consider something along these lines.

• Create a PostgreSQL database on a shared host.
• Create a table of integers. (See above.)
• Populate it using generate_series(). (Also above).
• Export the data as a text file.
• Download the text file.
• Import the text file into your own database.

Write a program

Writing a program is usually quick and easy. These generate integers from 1 to 10 using several programming languages. Each of them writes one integer per line to stdout. Redirect stdout to a file, and import the file.

Integers in C

#include <stdio.h>

int main (void) {
  int i;
  for (i = 1; i <= 10; i++) {
    printf("%d\n", i);
  }
  return 0;
}

Integers in Python

The endpoint is never part of a generated list in Python. That’s why we give it the range of 1 to 11.

for i in range(1, 11):
    print i

Integers in Ruby

In Ruby, this problem is a one-liner.

1.upto(10) {|i| puts "#{i}"}

My preferred alternative

When I can’t use something like PostgreSQL’s generate_series(), I prefer to use the Linux utility seq, and import the resulting file using the dbms’s bulk loader.

Use seq

The utility program seq ships with all current Linux distributions. It also ships with the current version of BSD and its descendants. On Windows, it’s available as part of the Cygwin project.

Generating integers from 1 to 10 is dead simple.

$ seq 1 10

Redirect stdout to a file, and import the file.

Import the file

Every SQL dbms ships with a bulk loader. In PostgreSQL, it’s copy. In MySQL, it’s mysqlimport. Having generated a file of integers, one per line, you can import them into any dbms.

$ seq 1 10 > integers
$ mysqlimport --user=mike --password=***** -L sandbox integers

The name of the file must match the name of the table. That command line imports the file “integers” into the table “integers” in my database “sandbox”.

Other methods

Spreadsheets are useful for both integers and dates. They’re especially good at date arithmetic.

Think about whether it makes sense to write a function or stored procedure that mimics the syntax and behavior of PostgreSQL’s generate_series() function.

---

About the author

Mike Sherrill is a SQL database developer, database administrator, software developer, and web developer living and working in the Midwestern United States. He has clients spanning the globe, kind of like ABC’s Wide World of Sports. Like the athletes, he regularly experiences both the thrill of victory and the agony of defeat.