Postgres 15

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?.

SELECT 
 ST_AsText(
  ST_PointZ(
        52.0,
        6.0,
        EXTRACT(EPOCH FROM '2023-05-23 10:22:18'::timestamp),
        4326)
  );


st_astext


POINT Z (52 6 1684837338)



SELECT 1


WITH j (geo) AS (
  VALUES ('POINT Z (52 6 1684837338)'::geometry)
)
SELECT ST_X(geo), ST_Y(geo), 
  to_timestamp(ST_Z(geo))
FROM j;


st_x
st_y
to_timestamp


52
6
2023-05-23 11:22:18+01



SELECT 1


SELECT 
  ST_AsGeoJSON(
   ST_MakeLine(
    ST_PointZ(52.0,6.0,EXTRACT(EPOCH FROM now()-'1 hour'::interval),4326),
    ST_PointZ(52.0,6.0,EXTRACT(EPOCH FROM now()),4326))
  );


st_asgeojson


{"type":"LineString","coordinates":[[52,6,1711408929.03185],[52,6,1711412529.03185]]}



SELECT 1


WITH j (geo) AS (
 VALUES
  (ST_MakeLine(
        ST_PointZ(52,6,EXTRACT(EPOCH FROM now()-'1 hour'::interval),4326),
        ST_PointZ(52,6,EXTRACT(EPOCH FROM now()),4326))),
    (ST_MakeLine(
        ST_PointZ(55,7,EXTRACT(EPOCH FROM now()-'2 hour'::interval),4326),
        ST_PointZ(55,7,EXTRACT(EPOCH FROM now()),4326)))
 )
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(j.*)::json)
    )
FROM j;


json_build_object


{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"LineString","coordinates":[[52,6,1711408929.032514],[52,6,1711412529.032514]]}, "properties": {}}, {"type": "Feature", "geometry": {"type":"LineString","coordinates":[[55,7,1711405329.032514],[55,7,1711412529.032514]]}, "properties": {}}]}



SELECT 1