1

I have a table which essentially contains 3 columns: ID, FIRST, LAST. They're all integers.

I'm wondering if there's a way to use generate_series() to get a query result where for each row in the original table, there are many rows containing numbers between FIRST and LAST?

E.g. for a row with data (42, 5, 8) the output contains rows (42, 5), (42, 6), (42, 7) and (42, 8).

0

In Postgres 10 or later it can be as simple as:

SELECT id, generate_series(first, last) AS nr
FROM   tbl;

In older versions it is not advisable to have set-returning functions in the SELECT list. See:

Rows where generate_series() returns nothing are excluded from the result this way (e.g. when last < first or either is NULL). The same is true for the more explicit CROSS JOIN syntax variant demonstrated by a_horse. May or may not be desirable. If it's not, use LEFT JOIN .. ON true instead:

SELECT t.id, g.nr
FROM   tbl t
LEFT   JOIN LATERAL generate_series(t.first, t.last) g(nr) ON true;

(LATERAL is a noise word in this case, since it's assumed for functions in the FROM list.)

See:

db<>fiddle here (demonstrating corner cases)

2

This can be achieved using generate_series()

with test_data (id, "first", "last") as (
   values (42, 5, 8), 
          (43, 6, 9)
)
select td.id, g.nr
from test_data td
  cross join lateral generate_series(td."first", td."last", 1) as g(nr)
order by td.id, g.nr;

Online example: https://rextester.com/TWE47868

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.