Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » postgresql error using lower/upper/ilike functions (Case insensitive searching postgresql)
postgresql error using lower/upper/ilike functions [message #6186] Sat, 04 March 2017 22:43 Go to next message
renato is currently offline  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?

Re: postgresql error using lower/upper/ilike functions [message #6187 is a reply to message #6186] Sun, 05 March 2017 05:43 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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%'


[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 Go to previous messageGo to next message
renato is currently offline  renato
Messages: 4
Registered: October 2016
Junior Member
Yes. It works!
Thanks!
Re: postgresql error using lower/upper/ilike functions [message #6285 is a reply to message #6186] Wed, 12 April 2017 16:12 Go to previous messageGo to next message
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.
Re: postgresql error using lower/upper/ilike functions [message #6286 is a reply to message #6285] Wed, 12 April 2017 18:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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?

Re: postgresql error using lower/upper/ilike functions [message #6318 is a reply to message #6286] Sun, 16 April 2017 06:47 Go to previous messageGo to next message
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.
Re: postgresql error using lower/upper/ilike functions [message #6321 is a reply to message #6318] Sun, 16 April 2017 08:24 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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?


Re: postgresql error using lower/upper/ilike functions [message #6322 is a reply to message #6321] Sun, 16 April 2017 11:21 Go to previous messageGo to next message
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.
Re: postgresql error using lower/upper/ilike functions [message #6332 is a reply to message #6322] Mon, 17 April 2017 04:49 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Do you have cookies enabled?

Re: postgresql error using lower/upper/ilike functions [message #6357 is a reply to message #6332] Fri, 28 April 2017 00:58 Go to previous messageGo to next message
Triolo is currently offline  Triolo
Messages: 1
Registered: April 2017
Junior Member
AJM wrote on Mon, 17 April 2017 04:49
Do you have cookies enabled?


How do you check?


Re: postgresql error using lower/upper/ilike functions [message #6358 is a reply to message #6357] Fri, 28 April 2017 04:38 Go to previous message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
It is a setting in your browser.

Previous Topic: Legacy file carryover
Next Topic: Regex and Sql Queries containing +
Goto Forum:
  


Current Time: Thu Mar 28 08:22:44 EDT 2024

Total time taken to generate the page: 0.02937 seconds