Skip site navigation (1) Skip section navigation (2)

Re: MySQL search query is not executing in Postgres DB

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: premanand <kottiprem(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: MySQL search query is not executing in Postgres DB
Date: 2012-02-17 07:45:46
Message-ID: 4F3E05AA.4000408@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Etsuro FujitaDate: 2012-02-17 07:50:50
Subject: Re: WIP: Collecting statistics on CSV file data
Previous:From: Jesper KroghDate: 2012-02-17 06:09:47
Subject: Re: Qual evaluation cost estimates for GIN indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group