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?

The SQL standard (and Postgres) uses single quotes for string constants http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS — , Jan 26 '21 at 11:37

score 2 · Answer 1 · answered Jan 26 '21 at 11:28

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.

answered Jan 26 '21 at 11:28

wildplasser

43,142
8
66
109

IMSoP · Accepted Answer · 2021-01-26T11:47:00.127

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'

The double quotes for identifiers are also defined in the SQL standard. — , Jan 26 '21 at 11:38
@a_horse_with_no_name Sure, but unless you're designing a new DBMS, that's less useful to know than what DBMSes actually support. That said, SQL Server does support it as an alternative, even though it won't be what gets generated by its built-in tools. — IMSoP, Jan 26 '21 at 11:39

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

2 Answers2