Thread: MySQL search query is not executing in Postgres DB

MySQL search query is not executing in Postgres DB

From
premanand
Date:
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


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/MySQL-search-query-is-not-executing-in-Postgres-DB-tp5492402p5492402.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: MySQL search query is not executing in Postgres DB

From
Adrian Klaver
Date:
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.klaver@gmail.com

Re: MySQL search query is not executing in Postgres DB

From
Marti Raudsepp
Date:
On Fri, Feb 17, 2012 at 17:13, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> 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.

Nope, trying to use CAST() in an interoperable manner is a lost cause.
Sadly MySQL and PostgreSQL don't agree over the cast types. MySQL's
CAST doesn't recognize varchar or text. Both support CHAR, but they
work differently (MySQL's char behaves like PostgreSQL's varchar):

CAST('foo' as char);
MySQL: 'foo'
PostgreSQL: 'f'

CAST('foo' as char(10))
MySQL: 'foo'
PostgreSQL: 'foo       ' (padded with spaces!)

The only 3 casts that *seem* to behave similarly are time, date and decimal.

You could abuse the concat function (present in PostgreSQL 9.1+) for
this purpose, but it's ugly:
SELECT ... WHERE concat(Table.ID) LIKE '1%'

Regards,
Marti

Re: MySQL search query is not executing in Postgres DB

From
Scott Marlowe
Date:
On Fri, Feb 17, 2012 at 9:01 AM, Marti Raudsepp <marti@juffo.org> wrote:
> On Fri, Feb 17, 2012 at 17:13, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>> 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.
>
> Nope, trying to use CAST() in an interoperable manner is a lost cause.
> Sadly MySQL and PostgreSQL don't agree over the cast types. MySQL's
> CAST doesn't recognize varchar or text. Both support CHAR, but they
> work differently (MySQL's char behaves like PostgreSQL's varchar):

Have you tried casting to varchar(1000) or something like that?

Re: MySQL search query is not executing in Postgres DB

From
Marti Raudsepp
Date:
On Fri, Feb 17, 2012 at 18:19, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Have you tried casting to varchar(1000) or something like that?

MySQL's CAST() doesn't accept varchar or varchar(N). It accepts char,
which behaves like varchar.

Regards,
Marti

Re: MySQL search query is not executing in Postgres DB

From
Alban Hertroys
Date:
On 17 February 2012 17:19, Scott Marlowe
> Have you tried casting to varchar(1000) or something like that?

Don't MySQL's varchars only go to 255? That's why every MySQL database
uses blobs for text data, isn't it?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: MySQL search query is not executing in Postgres DB

From
Scott Marlowe
Date:
On Fri, Feb 17, 2012 at 9:22 AM, Marti Raudsepp <marti@juffo.org> wrote:
> On Fri, Feb 17, 2012 at 18:19, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> Have you tried casting to varchar(1000) or something like that?
>
> MySQL's CAST() doesn't accept varchar or varchar(N). It accepts char,
> which behaves like varchar.

It's a shame you can't file a mysql bug report and get a patch in a
reasonable amount of time like you can with pgsql*.

(* I've filed two bug reports and gotten patches in < 24 hours both
times with pgsql.)

Re: MySQL search query is not executing in Postgres DB

From
Adrian Klaver
Date:
On 02/17/2012 08:01 AM, Marti Raudsepp wrote:
> On Fri, Feb 17, 2012 at 17:13, Adrian Klaver<adrian.klaver@gmail.com>  wrote:
>> 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.
>
> Nope, trying to use CAST() in an interoperable manner is a lost cause.
> Sadly MySQL and PostgreSQL don't agree over the cast types. MySQL's
> CAST doesn't recognize varchar or text. Both support CHAR, but they
> work differently (MySQL's char behaves like PostgreSQL's varchar):
>

Well I guess the choices are put back the implicit cast or change the
data type of the Table.ID column in Postgres to varchar.

>
>
> Regards,
> Marti


--
Adrian Klaver
adrian.klaver@gmail.com

Re: MySQL search query is not executing in Postgres DB

From
premanand
Date:
Hi Marti,

Thanks for your reply. It works as expected.

Regards,
Prem

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/MySQL-search-query-is-not-executing-in-Postgres-DB-tp5492402p5494971.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: MySQL search query is not executing in Postgres DB

From
Susanne Ebrecht
Date:
Am 17.02.2012 17:24, schrieb Alban Hertroys:
> On 17 February 2012 17:19, Scott Marlowe
>> Have you tried casting to varchar(1000) or something like that?
> Don't MySQL's varchars only go to 255? That's why every MySQL database
> uses blobs for text data, isn't it?
>

Yes.

--
Dipl. Inf. Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com


Re: MySQL search query is not executing in Postgres DB

From
Daniel Neugebauer
Date:
On 02/19/2012 12:05 AM, Susanne Ebrecht wrote:
> Am 17.02.2012 17:24, schrieb Alban Hertroys:
>> On 17 February 2012 17:19, Scott Marlowe
>>> Have you tried casting to varchar(1000) or something like that?
>> Don't MySQL's varchars only go to 255? That's why every MySQL database
>> uses blobs for text data, isn't it?
>>
>
> Yes.

Slightly off-topic but MySQL actually changed that behaviour in bugfix
release 5.0.3 according to their manual. I was surprised myself when a
colleague of mine used varchar(500) last week. Their upper limit is now
at 2^16-1 = 65535 bytes. I don't have a much advanced knowledge in DB
matters yet, but I usually decided for TEXT or MEDIUMTEXT types in MySQL
when I had to store longer text content, IMHO there's no need using
blobs for texts unless you want to keep admin frontends from displaying
the contents right away.