In PostgreSQL 10.0, I have a big table with two columns:

  ts timestamp with time zone NOT NULL,
  x integer NOT NULL

Now, if I have 100 given time points, how do I return exactly 100 x-es that are right before (<=) these time points (for each of those points)?

For just a single one this would be very easy:

SELECT x FROM xs WHERE ts <= '2018-03-13 11:41:47.167973+00' LIMIT 1

But how to do this efficiently for a 100? Something like:

    ('2018-03-13 11:41:47.167973+00', 
     '2018-03-13 11:41:47.198564+00', 
     '2018-03-13 11:41:47.555668+00',
     '2018-03-13 11:41:57.766888+00')

Obviously, there is an index on ts.

  • 2
    Do you have any unique constraints on the table? Also, your query for a single value doesn't have order by, so it can return any x where ts is less or equal to given, not the right before.
    – a1ex07
    Commented Mar 13, 2018 at 16:36
  • Oops, sorry about no ORDER BY! ts is not unique, but the I can select an arbitrary value (xs for the same ts will be the same or very close).
    – Michal Rus
    Commented Mar 14, 2018 at 11:13
  • 1
    Just remember that repeated calls may return different values if the result is not deterministic. Might cause confusion. So the safe approach is to make it deterministic to begin with. Commented Mar 14, 2018 at 14:17

1 Answer 1


I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all):

SELECT v.reference_ts, t.most_recent_x
      (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row
    , ('2018-03-13 11:41:47.198564+00') 
    , ('2018-03-13 11:41:47.555668+00')
    , ('2018-03-13 11:41:57.766888+00')
   ) AS v(reference_ts)
   SELECT x AS most_recent_x
   FROM   xs
   WHERE  ts <= v.reference_ts  -- ts is unique (?!); see below
   LIMIT  1
   ) AS t ON true;

A VALUES expression is one way to put your 100 timestamps into a derived table we can work with. The free-standing VALUES expression may require an explicit type cast in the first row. See:

There are others ways to pass your values, like an array (or an array literal cast to timestamptz[]) and unnest() in the query:

About the LATERAL subquery:

Don't forget the ORDER BY or you get arbitrary results - like @a1ex07 commented.

If ts is not unique, it's undefined which of the duplicates is returned, unless you add more ORDER BY items to make it deterministic. Like: ORDER BY ts DESC, x DESC to get the row with the biggest x from any set of peers. And make the index match.

An index on (ts) (like you mentioned) works wonders on performance.
Or, ideally, on (ts DESC, x) if preconditions for index-only scans are met.

dbfiddle here

  • 1
    Thank you very much! I thought this would be inefficient, but EXPLAIN says it’s not! Wonderful!
    – Michal Rus
    Commented Mar 14, 2018 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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