Select with null Postgresql field

Asked

Viewed 878 times

4

I have the following select 

SELECT u 
FROM User u 
WHERE lower(u.fullname) 
      || lower(u.username) 
      || lower(u.email)) LIKE %:pesquisa%

It turns out I want to do the search for any of the 3 existing fields, but email no required field.

If I do a survey that has no completed email it returns nothing.

postgresql

  • How would you feel about adding one || u.email is null?

  • hello bigown, I followed your suggestion, but what’s catching that I forgot to mention, is the Hibernate, take a look at the stacktrace. Exception Description: Syntax error Parsing [SELECT u FROM User u WHERE Lower(u.fullname) || Lower(u.username) || Lower(u.email) is null LIKE search]. Thanks for the help

  • Not what I told you to use, I’ll put the full syntax: SELECT u FROM User u WHERE Lower(u.fullname) || Lower(u.username) || Lower(u.email) || u.email is null LIKE %:search%

  • Thanks again for trying, but Exception persists, I will try other alternatives.

  • http://www.postgresql.org/docs/8.1/static/functions-conditional.html use COALESCE null string do not concatenate.

  • Motta, already tested with the Coalesce, but the Hibernate does not recognize the function. Thanks

Show 1 more comment

2 answers

1

Switch to the next:

SELECT u 
FROM User u 
WHERE lower(u.fullname) LIKE '%:pesquisa%'
OR lower(u.username) LIKE '%:pesquisa%'
OR lower(u.email) LIKE '%:pesquisa%'

  • Hello Gypsy, still still with error syntax Eclipselink, but thanks for the help. Look at stacktrace: Caused by: Exception [Eclipselink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.Jpqlexception Exception Description: Syntax error Parsing [SELECT u FROM User u WHERE Lower(u.fullname) || Lower(u.username) LIKE :searchString OR Lower(u.email) LIKE :searchString]. [27, 121] The Expression is not a Valid conditional Expression.

  • @Fernando Estranho. No mistake. I updated the answer.

  • Gypsy, thanks for the help, I did it like this: @Query("SELECT u FROM User u WHERE Lower(u.fullname) LIKE %:searchString% OR Lower(u.username) LIKE %:searchString% OR Lower(u.email) LIKE %:searchString%").

  • @Fernando is the same as the answer. Please see the tour to understand the site cycle. Thank you!

  • The like should not have been LIKE '%:survey%' ?

  • @Allanramos Yes, it’s true. I’ve corrected.

Show 1 more comment

0

In Hibernate there is no COALESCE function, instead you should use the nullif(column, 'Value if null')

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.