Why does my postgresql SELECT statement return 0 rows?

Question

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 'johnsmith@gmail.com' 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 | johnsmith@gmail.com | adsfpokasdf

My query is:

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

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

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

may be you have a leading or trailing whitespace in the email? what does `select email from users` return? — carpamon, May 13 '16 at 19:09
Another way to test [the suggested](http://stackoverflow.com/questions/37217565/why-does-my-postgresql-select-statement-return-0-rows#comment61966342_37217565) is to use `trim()` function: `WHERE trim(email) = 'johnsmith@gmail.com'`. — PM 77-1, May 13 '16 at 19:13

score 3 · Accepted Answer · answered May 13 '16 at 19:25

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.

score 0 · Answer 2 · answered Mar 11 '19 at 20:34

0

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 :)

answered Mar 11 '19 at 20:34

JenniferG

602
1
5
13

"*why I have to quote it*": https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Additionally: `'fieldId'` is a string constant in SQL, not a column name: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS – Mar 11 '19 at 21:02

score -2 · Answer 3 · edited May 23 '17 at 12:32

-2

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'johnsmith@gmail.com';

Lastly:

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

edited May 23 '17 at 12:32

Community

1
1

answered May 13 '16 at 19:14

Sturgus

666
4
18

Good thought - I gave this a try and it still didn't work. I deleted the original row, created it again with the same info, and when I queried for it again it worked. There must have been some character discrepancy that I'm not seeing. Thanks so much for your quick reply. – Jcr May 13 '16 at 19:21

Why does my postgresql SELECT statement return 0 rows?

3 Answers3