Generate int4range from a set of integer values selecting as a specific table and column

user_9712989 0
Viewed 151

I am trying to generate a set of int4range values, but instead of getting int4range, I'm getting type record. I can't seem to extract the range or cast to the range type.

I specifically want to keep the SELECT "app_table"."id" part of the sql, since I am trying to manipulate an ORM to map this to an application.

What I've tried:

SELECT "app_table"."id" FROM (
    SELECT id FROM (
        SELECT (lag(a) OVER()), a, '[)'
            FROM generate_series(0, 10, 1)
            AS a OFFSET 1) AS id
) AS app_table LIMIT 3;

What I get:

     id (type of record)
------------------------
1    (0,1,"[)")
2    (1,2,"[)")
3    (2,3,"[)")

Another attempt:

SELECT "app_table"."id" FROM (
    SELECT (lag(id) OVER()), id, '[)'
        FROM generate_series(0, 10, 1)
        AS id OFFSET 1
) AS app_table

What I get:

     id (type of integer)
------------------------
1    1
2    2
3    3

What I'm trying to achieve:

     id (type of int4range)
---------------------------
1    [0,1)
2    [1,2)
3    [2,3)

I'm sure this is something simple, and I've tried lots of variations, but I'm getting nowhere.

generate-series lag range casting postgresql
1 Answers
SELECT "app_table"."id"
FROM  (
   SELECT int4range(a, a+1) AS id
   FROM   generate_series(0, 2) a
   ) app_table;

db<>fiddle here

Produces your desired result.

To generate the type int4range from integer, use the corresponding function int4range().
Read the manual about Constructing Ranges and Multiranges.

You already got that right with a table alias:

I specifically want to keep the SELECT "app_table"."id" part of the sql

user_9398600
Related