SELECT problems with postgresql

Question

SELECT problems with postgresql

Navigation

#1 by (2 votes)

2

I am doing a migration from mysql to postgres, but there is a mistake, 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'

mysql postgresql select

asked by anonymous 09.08.2016 / 17:10

1 answer

How can I find out the method of http request by PHP? [duplicate] Difficulty of Abstraction in Use Cases

score 2 · Accepted Answer

Your error is because you want to use a columnar that does not exist in your context (table), that is, 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;