Re: MySQL search query is not executing in Postgres DB

On Friday, February 17, 2012 5:01:47 am 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.

Why not use:
" SELECT * FROM <Table-name> WHERE CAST(Table.ID as TEXT) LIKE '1%' "

as the MySQL query also? MySQL supports the CAST function.

If you want the implicit casts take a look at:

http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

This assumes you are running Postgres 8.4+ Be sure and read the warnings.

> http://archives.postgresql.org/pgsql-general/2011-01/msg00866.php
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/MySQL-search-query-is-not-executin
> g-in-Postgres-DB-tp5492402p5492402.html Sent from the PostgreSQL - general
> mailing list archive at Nabble.com.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com