CODEWITHSUNDEEP
sqlpostgresqlselectsyntax-error
Dan Rubio
Dan Rubio

Reputation: 4907

Can someone help point out to me what is wrong with this no brainer WHERE clause?

This is very simple but somehow I'm doing something wrong with this query on my database.

I have this query below:

SELECT login FROM accounts WHERE login = "loginname";

When I execute this query the result I get is this:

column "loginname" does not exist

This is a no brainer, why is this query not working properly? I have a login column and I know that this user exists because I've found this person with the rails console. Why is the login criteria referring to itself as a column?

Upvotes: 3

Views: 77

Answers (4)

MDschay
MDschay

Reputation: 321

It seems that the " " are the problem if you believe the documentation. Single quotes are required for string values.

Upvotes: 1

Anthony Grist
Anthony Grist

Reputation: 38345

From the PostgreSQL Documentation:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:

UPDATE "my_table" SET "a" = 5;

Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

So in your query "loginname" is the same as having loginname without quotes - it's attempting to refer to a column with that name. To make it a literal string, use single-quotes instead.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311508

Double quotes (") are used to refer to object names, in a case sensitive way. In this case, "loginname" is interpreted as a column name, and the query fails, since there is no such column. In order to refer to a string literal, you should use single quotes ('):

SELECT login FROM accounts WHERE login = 'loginname';
-- Here ---------------------------------^---------^

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

Try with single quotes '' if you are trying to match a string

SELECT login FROM accounts WHERE login = 'loginname';

Check the documentation

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Upvotes: 4

Related Questions