Works in MySQL but fails in Postgresql - having statement

Question

I have the query below which works in MySQL but fails in Postgresql with the following error:

ERROR:  column "status" does not exist

How do we implement this in Postgresql?

select
    devices.*,
    CASE
        WHEN devices.retired = false
        AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
        AND SUM(device_measurements.flow) > 0 THEN 'active'
        WHEN devices.retired = false
        AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
        AND SUM(device_measurements.flow) = 0 THEN 'online'
        WHEN devices.retired = false
        AND devices.last_reported_utc_at <= '2020-12-23 07:13:52' THEN 'offline'
        WHEN devices.retired = false
        AND devices.serial_number IS NULL THEN 'inactive'
        WHEN devices.retired = true THEN 'retired'
    END AS status,
    MAX(devices_meters.activated_at) AS activated_at,
    MAX(devices_meters.created_at) AS latest
from
    devices
    left join devices_meters on devices_meters.device_id = devices.id
    left join device_measurements on device_measurements.device_id = devices.id
group by
    devices.id,
    devices_meters.activated_at
having
    status = "offline"
order by
    devices.installed_at desc

String constants need to be enclosed in single quotes in SQL, `"offline"` is a column reference (in that context) and `'offline'` is a string constant — , Dec 24 '20 at 07:59

score 2 · Answer 1 · answered Dec 24 '20 at 07:34

There are several solutions.

Use a subquery
Use a CTE (WITH)
Use a lateral join

The latter has my preference, here is how I would implement it:

select
    devices.*,
    lt.status,
    MAX(devices_meters.activated_at) AS activated_at,
    MAX(devices_meters.created_at) AS latest
from
    devices
    left join devices_meters on devices_meters.device_id = devices.id
    left join device_measurements on device_measurements.device_id = devices.id
left join lateral (
  SELECT
    CASE
        WHEN devices.retired = false
        AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
        AND SUM(device_measurements.flow) > 0 THEN 'active'
        WHEN devices.retired = false
        AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
        AND SUM(device_measurements.flow) = 0 THEN 'online'
        WHEN devices.retired = false
        AND devices.last_reported_utc_at <= '2020-12-23 07:13:52' THEN 'offline'
        WHEN devices.retired = false
        AND devices.serial_number IS NULL THEN 'inactive'
        WHEN devices.retired = true THEN 'retired'
    END AS status
  ) AS lt ON TRUE
group by
    devices.id,
    devices_meters.activated_at
having
    lt.status = "offline"
order by
    devices.installed_at desc

Now although this solves the problem of making status available everywhere in the query, you have another problem with the GROUP BY which needs to include devices.* and lt.status, but that's beyond the scope of this question I presume.

I don't see how elements of the solution are beyond the scope of the question — Strawberry, Dec 24 '20 at 07:41
Because the question is "what is the syntax?" I don't know the columns in the `devices` table, also I don't understand the point of putting `devices.meters.activated_at` in the GROUP BY clause and then retrieve `MAX(activated_at)`. I gave the elements of answer I could, but I feel like I am not able to answer this question better than that :-( — Fabian Pijcke, Dec 24 '20 at 07:52
Well you know some of them. You could just use those. But, yeah, fair enough. — Strawberry, Dec 24 '20 at 07:57
Hey @Fabian Pijcke, thanks for the answer. Your query fails with `Query 1: ERROR: aggregate functions are not allowed in FROM clause of their own query level LINE 15: AND SUM(device_measurements.flow) > 0 THEN '...` — TheRealPapa, Dec 26 '20 at 02:10

Works in MySQL but fails in Postgresql - having statement

1 Answers1