CODEWITHSUNDEEP
postgresqlselect
Jcr
Jcr

Reputation: 93

Why does my postgresql SELECT statement return 0 rows?

Maybe I'm missing something painfully obvious but I'm trying to select from a table with a column called 'email' with type character(50).

I set up a simple test and i can successfully query every other column except for this one. For now the columns 'name' and 'password' are also of type character(50);

I'm copying and pasting the sample email '[email protected]' exactly as is listed in the database and I am sure there are no extra spaces.

Here is my table called 'users':

id | name |        email        | password
---------------------------------------------
10 | john | [email protected] | adsfpokasdf

My query is:

SELECT name FROM users WHERE email = '[email protected]';

Is there a problem with my choice of the column type?

Here is a picture: Why is this postgresql SELECT statement returning 0 rows?

Upvotes: 2

Views: 3918

Answers (3)

JenniferG
JenniferG

Reputation: 602

I had to change my where clause from 

SELECT "fieldId" FROM MyTable WHERE 'fieldId' = 'stringid'

to

SELECT "fieldId" FROM MyTable WHERE "fieldId" = 'stringid'

Basically the single quotes in the where clause to double. I am not sure why I have to quote it, but in some instances of postgres I have had to do this.

Hopefully this helps someone else :)

Upvotes: 0

jmelesky
jmelesky

Reputation: 3970

Maybe I'm missing something painfully obvious but I'm trying to select from a table with a column called 'email' with type character(50).

This is your issue. You want the type character varying (also known as varchar. The text type will work just as well, without a length check). character(50) will only hold strings of length 50, and will pad it with spaces to fill that out.

Re-create the table with the column types as varchar(50) or text, and try again. It should work closer to expectations.

Upvotes: 3

Sturgus
Sturgus

Reputation: 686

Maybe '@' is used for pattern-matching in PostgreSQL? If so, it might need the escape character preceeding it...

TRY:

SELECT name FROM users WHERE email = 'johnsmith\@gmail.com';

Also try (from THIS post):

SELECT name FROM users WHERE email = E'[email protected]';

Lastly:

SELECT name FROM users WHERE email = E'johnsmith\@gmail.com';

Upvotes: -2

Related Questions