Wednesday, July 14, 2010

ILIKE keyword in PostgreSql

ILIKE keyword in PostgreSQL provides case insensitive  search.

PostgreSql provides case sensitive as well as case insensitive pattern matching, traditional LIKE operator do the same as other rdbms, ILILKE operator used for case insensitive pattern matching.

ex : SELECT name FROM employee WHERE name LIKE 'AN%'

query will display only ANTRO, ANBU, ANTUN etc..

ex : SELECT name FROM employee WHERE name ILIKE 'AN%'

query will display name start with 'an' irrespective case, ANTRO, antTro, ANBU,AnTUN, ANTUN etc..

===================================================

Issue :: DATE data type with pattern matching operator (LIKE) in version 8.3 later.

Date data type with LIKE operator works fine for the prior to 8.3 version.

for ex : SELECT name FROM employee WHERE date_of_birth LIKE '2007-01-19%'; // older version works fine

query will display all the name of date_of_birth falls on 2007-01-19.

but version 8.3 will gives hint like
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

and
This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to inappropriate behaviours. Prior to 8.3, your timestamps was
casts to text implicitly.

to get the same query output you may use "date_trunc" function its works like normal "trunc" function.

for ex : SELECT name FROM employee WHERE date_trunc('day',date_of_birth) = '2007-01-19';
query will round with date and display all the name.

note : instead of field ' day ' in date_trunc function you may use

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

function would round according to the field value.

4 comments:

Anonymous said...

Thanks, it's better than Postgre manuals

dataanalytics said...

thanks dude..

Anonymous said...

spend half a day on date with the HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
THANKS

dataanalytics said...

thank you..