2

Given the table:

CREATE TABLE node (
    id serial PRIMARY KEY,
    created timestamp without time zone NOT null,
    path ltree NOT null UNIQUE
);

And the records:

INSERT INTO node (id, path, created)
VALUES
  (1, '1', '2010-01-01 12:00'),
  (2, '1.2', '2010-01-01 13:00'),
  (3, '1.2.3', '2010-01-01 14:00');

How do I get the valid range for each node based on the node's created date and its' parent's created date?

For instance, the ranges for the given path 1.2.3 would be:

id | path  | valid
------------------------------------------------------------------
 1 |     1 | tsrange('2010-01-01 12:00', '2010-01-01 13:00', '[)')
 2 |   1.2 | tsrange('2010-01-01 13:00', '2010-01-01 14:00', '[)')
 3 | 1.2.3 | tsrange('2010-01-01 14:00', 'infinity', '[)')
  • You mean, "based on the node's created date and its' child's created date". What if there's more than one immediate child? – mustaccio Jan 15 at 14:21
  • In this case, I always want to specify a specific path, so different immediate children would be different queries. – travis.paxton Jan 15 at 14:29
1

I cannot think of a smarter way than a self-join with all nodes that have a path just one element longer:

SELECT n1.id, n1.path,
       tsrange(n1.created, max(n2.created)) AS valid
FROM node AS n1
   LEFT JOIN node AS n2
      ON n2.path ~ (n1.path::text || '.*{1}')::lquery
GROUP BY n1.id;

 id | path  |                     valid                     
----+-------+-----------------------------------------------
  1 | 1     | ["2010-01-01 12:00:00","2010-01-01 13:00:00")
  2 | 1.2   | ["2010-01-01 13:00:00","2010-01-01 14:00:00")
  3 | 1.2.3 | ["2010-01-01 14:00:00",)
(3 rows)
|improve this answer|||||
1

I was playing around a little more and found a windowing function that I think might work as well. I'm not sure if it's more or less performant than Laurenz's solution.

SELECT
  id, path, tsrange(
    created,
    COALESCE(
      lag(created) OVER (ORDER BY created DESC),
      'infinity'
     )
  ) AS valid
FROM node
WHERE path @> '1.2.3'
ORDER BY path;

Latest query:

SELECT
    id,
    path,
    tsrange(created, lag(created, -1, 'infinity') OVER (ORDER BY created)) AS valid
FROM
    node
WHERE
    path @> '1.2.3';
|improve this answer|||||
  • You can get rid of the coalesce() if you use the three argument version of lag() instead: lag(created, 1, 'infinity') over (...) – a_horse_with_no_name Jan 15 at 9:49
  • Oh thanks, I didn't know about the parameters! I was able to clean up the query a little bit more by setting the offset to -1. – travis.paxton Jan 15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.