Lateral join fails on trivial example

Question

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?

Don't use commas in the `FROM` clause. And `lag()` in a `FROM`-less query seems pointless, you could just use `NULL` literally there. — sticky bit, Jul 20 '21 at 10:53

score 1 · Accepted Answer · edited Jul 20 '21 at 12:32

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.

I see... could you also explain why `generate_series` cannot be used in the SELECT, or point me to an explanation. — mikael, Jul 20 '21 at 11:53

Lateral join fails on trivial example

1 Answers1