postgresql: how to SELECT entries that satisfy a condition

Question

I am trying to select the entries that have the same text in the "email" column of my postgreSQL table. I am completly new to database and this is the first database I ever created, so sorry if it's a silly question. My database has 3 columns: key, user_mails and json_adress. I tried

$existent= "SELECT * FROM 
           (SELECT public.account_recover_users.* AS keys,emails,jsons
           FROM public.account_recover_users)
           WHERE emails='$email'";

but I guess I am mistaking somewhere.

Please help, I am trying to learn and I got a bit stuck.

are you getting an error? what exactly is going wrong? i do hope you're using some kind of php/postgres interface, as assigning a sql statement to a variable isn't enough to actually query a database. showing more of your code would be helpful. also, your query could be greatly simplified: `"SELECT * FROM public.account_recover_users WHERE user_mails='$email'"` — user428517, May 08 '13 at 16:46
@sgroves ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. — Cornelia Secelean, May 08 '13 at 16:48
1. you should try your queries manually (or via phpmyadmin) in mysql 2. be aware (seriously) of SQL injection, use prepared statements — leonbloy, May 08 '13 at 16:56
@sgroves oops... well, `s/mysql/psql/`, `s/phpmyadmin/pgAdmin/` — leonbloy, May 08 '13 at 17:33
`My database has 3 columns` ... A database does not have columns. Start reading about [the basics here](http://www.postgresql.org/docs/current/interactive/sql-syntax.html). — Erwin Brandstetter, May 08 '13 at 19:45

user428517 · Answer 1 · 2013-05-08T16:59:36.417

The reason you got the error ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo is because you have to give an alias (nickname) to any subquery you use. So, just do what the error message hint tells you to do:

"SELECT *
FROM (
  SELECT public.account_recover_users.* AS keys, emails, jsons
  FROM public.account_recover_users
) as subq
WHERE emails='$email'"

But you don't need a subquery at all. This could be simplified to just:

"SELECT * FROM account_recover_users WHERE user_mails='$email'"

If you want to rename (i.e. give an alias to) your columns upon selection, I wouldn't use a subquery. Try:

"SELECT key as keys, user_mails as emails, json_adress as jsons
FROM account_recover_users
WHERE emails='$email'"

I don't really recommend this, though. If you're just going to give an alias to every column, why not rename the columns in the database?

score 0 · Answer 2 · answered May 08 '13 at 16:55

Because you're referencing public.account_recover_users in a derived table (in brackets), you need to give the derived table an alias which can be almost anything.

$existent= "SELECT * FROM 
(SELECT public.account_recover_users.* AS keys,emails,jsons 
FROM public.account_recover_users) as aru   
WHERE emails='$email'";

In your case though I don't think you need a derived table at all. You could just write.

$existent = "SELECT key as keys, user_mail as emails, json_address as jsons 
FROM public.account_recover_users 
WHERE emails='$email'";

score 0 · Answer 3 · answered May 08 '13 at 16:59

You don't need the subquery... (Alias it if you actually need one).

Nor do you need public, since it's in your search path by default.

Nor do you need the fields here, since you're selecting them all.

SELECT * FROM account_recover_users WHERE user_mails='$email'

Oh, and don't forget to escape $email... Look into PDO::prepare().

score 0 · Accepted Answer · edited May 23 '17 at 12:13

I am writing another answer, because the currently accepted answer is wrong in several respects. Neither of the two presented queries can work, even though the OP incorrectly accepted the answer.
The original query couldn't work for several reasons:

As was mentioned (and the error message clearly states): a subquery requires an alias.
As was also mentioned, a subquery is just pointless for the simple task.
This construct is nonsense: public.account_recover_users.* AS keys You can't apply an alias after expanding * to the list of columns. Postgres just ignores it. It might throw an exception in future releases.
According to your description, existing columns are named key, user_mails and json_adress (sic!). emails or jsons are just invalid references.

Your absolutely basic query should be:

"SELECT * FROM public.account_recover_users WHERE emails = '$email'"

You can rename columns in the query by appling column aliases, but your WHERE clause cannot refer to output columns (aliases), it has to refer to input columns:

"SELECT key, user_mails AS email, json_adress AS jsons
 FROM   public.account_recover_users
 WHERE  user_mails = '$email'"

Detailed explanation in this related answer:
How to re-use result for SELECT, WHERE and ORDER BY clauses?

Thank you, the detailed explanation is really good, as is your answer. — Cornelia Secelean, May 09 '13 at 09:52

postgresql: how to SELECT entries that satisfy a condition

4 Answers4