Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl (id int, first int, last int);
INSERT INTO tbl VALUES
  (42,  5,  8) 
, (43, -3, -2)
, (44,  8,  5)     -- last < first
, (45,  1,  NULL)  -- with NULL value
;
4 rows affected

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


id
nr


42
5

42
6

42
7

42
8

43
-3

43
-2


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


id
nr


42
5

42
6

42
7

42
8

43
-3

43
-2

44
null

45
null

id	nr
42	5
42	6
42	7
42	8
43	-3
43	-2
44	null
45	null