On 17.02.2012 07:33, premanand wrote:
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.
http://archives.postgresql.org/pgsql-general/2011-01/msg00866.php
You can use CREATE CAST
(http://www.postgresql.org/docs/current/static/sql-createcast.html). Or
you can create the operator "integer ~~ text" with CREATE FUNCTION +
CREATE OPERATOR. The latter would match fewer cases, which would reduce
the chances of introducing subtle bugs elsewhere in your application.
Of course, the best fix would be to change your queries. It's quite
sloppy to rely on "integer LIKE text" without an explicit cast in the query.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
