SELECT issues with postgresql

Question

I am making a migration from mysql to postgres, but there is an error happening, not much experience with postgres:

SELECT

SELECT m.* , (SELECT c.id 
                FROM corridas c 
                WHERE c.motoqueiro = m.id 
                AND (c.status = 0 OR c.status = 1)) as total 
FROM motoqueiros m 
WHERE now() - INTERVAL '17888 SECONDS' <= data_update 
AND count(total) = 0;

Error message:

ERROR: column "total" does not exist LINE 2: ... INTERVAL '17888 SECONDS' <= data_update AND Count(total) = 0 ^

********** Error **********

ERROR: column "total" does not exist SQL state: 42703 Character: 198

someone can help me?

Answer 1

Your mistake is because you want to use a columnar that does not exist in your context(table), ie the total column does not exist yet, you are renaming it from your sub select, if you want to use it so you will have to do a sub select or use your select instead of this column.

SELECT m.* , (SELECT c.id 
                FROM corridas c 
                WHERE c.motoqueiro = m.id 
                AND (c.status = 0 OR c.status = 1)) as total 
FROM motoqueiros m 
WHERE now() - INTERVAL '17888 SECONDS' <= data_update 
AND (SELECT count(c.id)
                FROM corridas c 
                WHERE c.motoqueiro = m.id 
                AND (c.status = 0 OR c.status = 1) = 0;

Or do the sub select

select * from 
(
    SELECT m.* , (SELECT count(c.id) 
                    FROM corridas c 
                    WHERE c.motoqueiro = m.id 
                    AND (c.status = 0 OR c.status = 1)) as total 
    FROM motoqueiros m 
    WHERE now() - INTERVAL '17888 SECONDS' <= data_update 

)sub
where sub.total = 0;

Answer 2

I had the same problem in version 4.30, and found that the names of the columns need to be double quotes.

Follow my example:

SELECT round (avg (f.salario_hora)), e.estado 
FROM "Cap16"."TB_FUNC" f, "Cap16"."TB_ENDERECO" e
WHERE f."ID" = e."id_func"
GROUP BY e."estado"