How to turn off postgresql case sensitive search?

Question

I have a lot of sql query command that use "WHERE" clause, I have just wondered that my postgresql searching was case sensitive.

for example:

 Select * From myarea Where area_name = 'Jawa Barat1' --> not found
 Select * from myarea Where area_name = 'jawa barat1' --> found

How to turn off the case sensitive searching in postgresql?

Please don't suggest me to change the sql command to set to lowercase at both side.

expect postgresql to in-case-sensitive searching (Mother = mother)

the datatype citext support case sensitive search, https://www.postgresql.org/docs/9.1/citext.html — Vivek, Jul 04 '19 at 10:24

S-Man · Accepted Answer · 2019-07-04T07:22:59.530

5

Using ILIKE comparator, which (as the LIKE comparator) works also with wildcards (see fiddle)

Select * From myarea Where area_name ILIKE 'Jawa Barat1'

Or normalize string with lower() which converts all letters to non-capitals. You have to use it for both comparing literals:

Select * From myarea Where lower(area_name) = lower('Jawa Barat1')

edited Jul 04 '19 at 07:22

answered Jul 04 '19 at 07:15

S-Man

1

is there any database setup for turning off the case sensitive search?.. I have a lot of sql commands need to be adjusted :( – Donald Jul 04 '19 at 07:19

1 Answers1