Friday, September 21, 2012

PostgreSQL : No operator matches the given name and argument type(s).



HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts

this information related to PostgreSQL version,

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.

No comments: