Explode rows with number ranges into one row per number-CodePudding

My table looks like this:

from	to	status
1	3	invalid
10	15	valid

How can I efficiently SELECT or produce a result like this:

serial	status
1	invalid
2	invalid
3	invalid
10	valid
11	valid
12	valid
13	valid
14	valid
15	valid

CodePudding user response：

Use lateral join.

with t ("from", "to", status) as (values (1, 3, 'invalid'), (10, 15, 'valid'))
select l.val serial, t.status status
from t 
cross join lateral generate_series(t."from", t."to", 1) as l(val);

CodePudding user response：

Using a calendar table approach we can try:

WITH nums AS ( 
    SELECT generate_series(1, 15) AS num
)

SELECT n.num AS serial, t.status
FROM nums n
INNER JOIN yourTable t
    ON n.num BETWEEN t."from" AND t."to"
ORDER BY n.num;

Demo

Note in the event that your actual data could have from-to ranges which might overlap, you may want to consider adding a primary key to your table. This primary key could then be used in ordering the output as you want to see it.

CodePudding user response：

first CTE is your table,so:

with _data as (
select * from (values (1,3,'invalid'), (10,15,'valid')) as t("from","to","status")
)

SELECT generate_series(_data.from, _data.to) AS serial,
_data.status
from _data

produces

1   invalid
2   invalid
3   invalid
10  valid
11  valid
12  valid
13  valid
14  valid
15  valid

CodePudding user response：

A generalized solution would require a recursive query:

base step gathers current records
recursive step adds 1 to "from", till "from" reaches the "to" value.

WITH RECURSIVE cte AS (
    SELECT * FROM tab
    UNION ALL
    SELECT from_   1 AS from_, to_, status
    FROM cte
    WHERE from_   1 <= to_ 
)
SELECT from_ AS serial, status 
FROM cte 
ORDER BY from_, to_

Check the demo here.

CodePudding user response：

Except for the column that is named from (reserved keyword), the query is simple:

SELECT generate_series("from", "to") AS serial, status
FROM T