SELECT query without FROM clause with a million rows

I’m trying to generate a million rows for a database table using SQL. The logic for the columns is in place, but I’m not sure how to go about generating a large number of rows without having a FROM clause.

I’m aware of how to write the SQL query without a FROM clause – select 'a', 'b' – but this generates only a single row. Is there a way to use this to generate a million rows?

I’ve also tried querying from existing tables that contain data, but I don’t have a table that contains a million rows, so the number of results is always restricted to the size of the tables.

How do I generate a million rows of data without having a FROM clause?

Answer

Use generate_series():

select 'a', 'b'
from generate_series(1, 1000000);