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.