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 t (gid int, geom geometry(linestring,4326));
INSERT INTO t VALUES
(1,'SRID=4326;LINESTRING(1 1,2 2,3 3,4 4)'),
(2,'SRID=4326;LINESTRING(3 3,7 7,4 4)'),
(3,'SRID=4326;LINESTRING(5 5,6 6)');
CREATE TABLE
INSERT 0 3
WITH j AS (
SELECT gid, ST_MakeLine(j.geom,LEAD(j.geom) OVER w) AS line
FROM t, ST_DumpPoints(geom) j (path,geom)
WINDOW w AS (PARTITION BY gid ORDER BY j.path
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
)
SELECT ST_AsText(ST_Collect(line)) FROM j
GROUP BY gid;
| st_astext |
|---|
| MULTILINESTRING((1 1,2 2),(2 2,3 3),(3 3,4 4)) |
| MULTILINESTRING((3 3,7 7),(7 7,4 4)) |
| MULTILINESTRING((5 5,6 6)) |
SELECT 3