Radicore Forum: Bug Reports » postgresql error using lower/upper/ilike functions

Home » RADICORE development » Bug Reports » postgresql error using lower/upper/ilike functions (Case insensitive searching postgresql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

postgresql error using lower/upper/ilike functions [message #6186]

Sat, 04 March 2017 22:43

renato
Messages: 4
Registered: October 2016

Junior Member

Hi Tony,

I'm trying to search case insensitive in PostgreSQL. Postgresql doesn't allow you to make text searching case insensitive.
You have to resort to either citext or lower/upper functions in your query. I don't like any of the options.
Radicore isn't allowing the syntax of postgresql lower function in combination with a LIKE.
It also doesn't recognize the 'ILike' operator.

The following throws a "Cannot extract token from:'%hello%'" error:

$where = "lower(product_name) LIKE '%hello%'";

'product_name' is a column in the database.
Seems to happen in the where2indexArray function from include.library.inc

It does however work with the '=' operator.

$where = "lower(product_name) = '%hello%'";

Of course the wild cards wouldn't work.

Tree questions:
1. How can I fix this?
2. Is there another way I can do case insensitive searching on texts in Radicore with pgsql?
3. Is there a way to hook into the default search to change the query to wrap the column name with lowar?

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6187 is a reply to message #6186]

Sun, 05 March 2017 05:43

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

I have amended include.library.inc so that it will allow both of the following in the WHERE string:

lower(product_name) LIKE '%hello%'"
product_name ILIKE '%hello%'

Attachment: include.library.zip
(Size: 57.29KB, Downloaded 1203 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

[Updated on: Mon, 06 March 2017 03:40]

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6189 is a reply to message #6187]

Mon, 06 March 2017 12:39

renato
Messages: 4
Registered: October 2016

Junior Member

Yes. It works!
Thanks!

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6285 is a reply to message #6186]

Wed, 12 April 2017 16:12

apmuthu
Messages: 30
Registered: April 2017
Location: Chennai Singapore

Member

This updated file include.library.inc appears to be missing in the April 2017 release.

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6286 is a reply to message #6285]

Wed, 12 April 2017 18:10

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

I have tested that file and cannot reproduce the problem. What is the WHERE string which you are using?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6318 is a reply to message #6286]

Sun, 16 April 2017 06:47

apmuthu
Messages: 30
Registered: April 2017
Location: Chennai Singapore

Member

The file attached to your post (2017-03-05) and that in the 2017-04-02 distribution are different. Attached both here. Kindly reconcile.

Attachment: Both_Includes.zip
(Size: 111.85KB, Downloaded 1119 times)

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6321 is a reply to message #6318]

Sun, 16 April 2017 08:24

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

They are different because I made some other changes since 2017-03-05.

I cannot reproduce this problem in my development environment. What is the WHERE string which you are using?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6322 is a reply to message #6321]

Sun, 16 April 2017 11:21

apmuthu
Messages: 30
Registered: April 2017
Location: Chennai Singapore

Member

I haven't checked out radicore fully yet and there is no WHERE string that causes any error yet. I have been looking at recent posts and major releases since 2006, tracing it's evolution atleast after coming to radicore.org.

I find that once logged in, if we enter the login page it expects us to login again - no session persistence yet.

Report message to a moderator