Column doens't exists in PostgreSQL (WHERE column_name = column_value)

Question

I have the following table in PostgreSQL:

id and grade are INTs, note and subject both VARCHARs

When I run the command:

SELECT * FROM grades 
WHERE subject = "latin";

I get the following error:

In pgAdmin4: ERROR: column "latin" does not exist LINE 2: WHERE subject = "latin" ^ SQL state: 42703 Character: 37

And in cmd: ERROR: column "latin" does not exist LINE 1: SELECT * FROM upisi WHERE subject = "latin";

I'm coming from MySQL so I thought this would work. Works fine if I put grade = something in the WHERE clause. Any idea why this might be the case?

Answer 1

It's as simple as the wrong type of quote marks. You wanted:

SELECT * FROM grades 
WHERE subject = 'latin';

To explain:

• Single quotes, like 'latin', are the standard way to write a string in standard SQL, and should work on all DBMSes.
• Double quotes, in Postgres and some other DBMSes, are a way of quoting identifiers - so if your column name had a space in for some reason (there's not many good reasons to do it, but it's possible), then you might write SELECT * FROM grades WHERE "subject name" = 'latin' - "subject name" is the name of the column, 'latin' is a string.

Although double quotes are in the SQL standard, other DBMSes use different syntax for quoting identifiers, so may treat double quotes as an alternative for writing strings.

-- Postgres (also works in Microsoft SQL Server, but isn't the default style)
SELECT * FROM grades WHERE "subject name" = 'latin'
-- MySQL
SELECT * FROM grades WHERE `subject name` = 'latin'
-- Microsoft SQL Server
SELECT * FROM grades WHERE [subject name] = 'latin'

But if you always use single quotes for strings, and avoid names that need quoting, you'll run into fewer problems.

-- Works pretty much everywhere
SELECT * FROM grades WHERE subject = 'latin'

Answer 2

You need single quotes for character constants. (double quotes are used to quote identifiers)

---

SELECT * FROM grades 
WHERE subject = 'latin';

---

If you use WHERE subject = "latin", the DBMS expects "latin" to be a column name, which it is not.