Question 90105 in PostgreSQL Question posted in
The official documentation can be found here.

Explode rows with number ranges into one row per number – Postgresql

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
Tags:

5

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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);
    Login or Signup to reply.
  4. 0

    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
    Login or Signup to reply.
  5. 0

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

    SELECT generate_series("from", "to") AS serial, status
FROM T
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search