leofontes leofontes - 2 months ago 7
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

Your quotes are wrong:

SELECT COUNT(*)
FROM "diets" JOIN
     "meals" 
     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.