Column not found in postgres

Question

I am trying to join two tables together using what they both have in common but I keep getting a particular column in not found and I can clearly see that the column is there.

Here is my script:

SELECT "productId", "unitId", SUM(quantity) 
    FROM consumable_pack_item INNER JOIN product ON consumable_pack_item.productId = product.id 
        GROUP BY "productId", "unitId";

Error:

ERROR:  column consumable_pack_item.productid does not exist
LINE 2:  FROM consumable_pack_item INNER JOIN product ON consumable_...
                                                         ^
HINT:  Perhaps you meant to reference the column "consumable_pack_item.productId".
SQL state: 42703
Character: 95

This is a faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Sep 23 '21 at 11:15

score 1 · Answer 1 · answered Sep 23 '21 at 10:51

You need to know that "productId" and productId are not the same thing. You defined the first to be case-sensitive by using double quotes.

In general, you should avoid using double quotes when defining columns and tables. You have already committed this transgression, so you need to continue to use double quotes everywhere where you reference the columns:

SELECT cpi."productId", "unitId", SUM(quantity) 
FROM consumable_pack_item cpi INNER JOIN
     product p
     ON cpi."productId" = p.id 
GROUP BY cpi."productId", "unitId";

Note that I added table aliases, so the query is easier to write and read. I also started to qualify the column names. If you have more than one table reference, I strongly advise you to qualify all column names in a query as a good habit to follow.

Column not found in postgres

Here is my script:

Error:

1 Answers1