Search code examples
postgresqllateral-join

Lateral join fails on trivial example

I am trying to create a timeseries and a lag of it at the same time using lateral:

dates as (
  SELECT generate_series(max(timestamp), min(timestamp), '1 week'::interval) as ts
  FROM batches,
  LATERAL (SELECT lag(ts) over (order by ts)) as prev_ts
),

For some reason I get ERROR: column "ts" does not exist. What is going on?

Solution

  • Table functions like generate_series belong into the FROM clause, since they return a table rather than a single value:

    SELECT g.ts,
       lag(g.ts) OVER (ORDER BY g.ts) AS prev_ts
FROM (SELECT max(timestamp) AS lts, min(timestamp) AS uts
      FROM batches) AS subq
   CROSS JOIN LATERAL
      generate_series(lts, uts, '1 week'::interval) as g(ts);

    You get the error not because you used generate_series in the SELECT list, which is allowed, but might have unexpected semantics, but because ts is defined as an alias in the SELECT list rather than in FROM. Remember that FROM is calculated before the SELECT list, so you cannot use aliases created in the latter in the former.