Why is my postgres lateral subquery failing?

Question

I'm trying to run the following query with postgres's support for LATERAL subqueries:

with s as
    (
        select
            tagValues ->> 'mode' as metric
            , array_agg(id) as ids
        from
            metric_v3.v_series
        where
            name = 'node_cpu'
        group by 1
    )
select
    t.starttime
    , s.metric
    , t.max
from
    s, lateral (
        select
            d.starttime
            , max(d.max) as max
        from
            metric_v3.gaugedata d
        where
            d.starttime >= '2020-01-17T00:00Z' AND  d.starttime < '2020-01-24T00:00Z'
            and d.seriesid in s.ids
        group by 1
    ) t
order by 1,2;

It fails with, where s relates to the reference in the lateral subquery's where clause.

SQL Error [42601]: ERROR: syntax error at or near "s"

I have tried different methods for the lateral query, but I always get the same error. I don't know what I'm missing.

If I run the CTE expression and select s.* from it, I get the expected results, so that part is working fine.

I'm running Postgres 11.6 on CentOS.

Laurenz Albe · Accepted Answer · 2020-01-29 07:01:25Z

1

You can't use IN with an array. You need to use the ANY operator:

and d.seriesid = any(s.ids)

edited Jan 29, 2020 at 7:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Jan 29, 2020 at 6:52

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is my postgres lateral subquery failing?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related