SQL: Select a list of numbers from "nothing"

postgresql, sequence, sql, sqlite

Solution

Thanks for all answers! Following the discussion I realized that using a numbers table is not too complicated and works well and fast on both/many platforms:

CREATE TABLE integers (i integer);
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
...
INSERT INTO integers (i) VALUES (9);
SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
  CROSS JOIN integers AS tens
  CROSS JOIN integers AS hundreds

You just create this table once and can use it whenever you need a range of numbers.

Problem

What is a fast/readable way to SELECT a relation from "nothing" that contains a list of numbers. I want to define which numbers by setting a start and end value. I am using Postgres SQL and SQLite, and would be interested in generic solutions that will work on both/many platforms. Desired output relation: ``` # x 0 1 2 3 4 ``` I know that I can SELECT a single row from "nothing": `SELECT 0,1,2,3,4` But this selects the values as columns instead of rows and requires to specify all values in the query instead of only using my start and end values: `0` and `4`. In Postgres you have a special `generate_series` function for this case: ``` SELECT * FROM generate_series(0,4) x; ``` This works nicely but is non-standard. I can also imagine some complicated solutions using temporary tables, but I would like to have something generic AND simple like: ``` SELECT * FROM [0..4] ``` Maybe using the `SEQUENCE` statement or some magic combination of `SELECT 0` and `SELECT 4`?

Original source