leofontes leofontes - 1 year ago 89
SQL Question

Postgres SQL query doesn't identify the column

I'm having a huge problem with my query, for some reason I just can't get one of the WHERE clauses to work.

This is my SQL:

SELECT COUNT(*) FROM "diets" JOIN "meals" on "idDiet" = "dietId"
WHERE kcal != 0 AND "diets.createdAt" > '2016-10-2'
GROUP BY "userIdUser" HAVING count(*) >= 5;

And my error:

ERROR: column "diets.createdAt" does not exist

My scheme for both tables:

enter image description here
enter image description here

Any idea on what I must do for this query to work? Thank you very much, if more information is needed please let me know.

Answer Source

Your quotes are wrong:

FROM "diets" JOIN
     ON "idDiet" = "dietId" 
WHERE kcal <> 0 AND "diets"."createdAt" > '2016-10-2' 
GROUP BY "userIdUser"
HAVING count(*) >= 5;

The double quotes go around an identifier. A qualified column reference such as diets.createdAt consists of two identifiers, so each needs to have the quotes (if you have them at all).

Otherwise, you are referring to a column whose name is "diets.createdAt". That is, the column name would have a period in it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download