Unanswered: MySQL search query is not executing in Postgres DB
In MySQL the below query is executing properly.
SELECT * FROM <Table-name> WHERE (Table.ID LIKE '1%')
But when i try to execute the above query in Postgres, i get the following Exception "org.postgresql.util.PSQLException: ERROR: operator does not exist: integer ~~ unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts".
If i convert the same query " SELECT * FROM <Table-name> WHERE CAST(Table.ID as TEXT) LIKE '1%' ". This gets executed directly in Postgres DB. But i need some query which implicitly type cast in DB, which allows me to execute the MySQL query without any Exception. Because i remember there is a way for integer to boolean implicit type cast. Please refer the following link. Update existing system explicit cast to make it implicit
Did you read the very first response by Tom Lane in the archived thread that you referenced?
Originally Posted by Tom Lane
You will likely find that this is a really bad idea. Implicit
casts between fundamentally different datatypes are *dangerous*.
They tend to result in either surprising query behaviors or unexpected
"operator is not unique" failures. Fixing the app would be a lot safer
in the long run.
Think about it. If you were to break pg in an attempt to make it adhere to MySQLs loose convention, it would be broken everywhere. So, at some other place/time, when you're looking for a string value, and you've inadventently supplied a numeric search criteria, pg won't throw an exception as it should. Instead, it will blindly accept your invalid input as valid, and give you a result that may or may not be valid. The key point here, is that you can't KNOW that the result is valid or not!
Last edited by loquin; 02-27-12 at 18:13.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert