Re: MySQL search query is not executing in Postgres DB

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