Quantcast

MySQL search query is not executing in Postgres DB

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

MySQL search query is not executing in Postgres DB

premanand
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Adrian Klaver-3
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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Marti Raudsepp
On Fri, Feb 17, 2012 at 17:13, Adrian Klaver <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Scott Marlowe-2
On Fri, Feb 17, 2012 at 9:01 AM, Marti Raudsepp <[hidden email]> wrote:

> On Fri, Feb 17, 2012 at 17:13, Adrian Klaver <[hidden email]> 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?

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Marti Raudsepp
On Fri, Feb 17, 2012 at 18:19, Scott Marlowe <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Alban Hertroys-4
In reply to this post by Scott Marlowe-2
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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Scott Marlowe-2
In reply to this post by Marti Raudsepp
On Fri, Feb 17, 2012 at 9:22 AM, Marti Raudsepp <[hidden email]> wrote:
> On Fri, Feb 17, 2012 at 18:19, Scott Marlowe <[hidden email]> 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.)

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Adrian Klaver-3
In reply to this post by Marti Raudsepp
On 02/17/2012 08:01 AM, Marti Raudsepp wrote:

> On Fri, Feb 17, 2012 at 17:13, Adrian Klaver<[hidden email]>  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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

premanand
Hi Marti,

Thanks for your reply. It works as expected.

Regards,
Prem
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Susanne Ebrecht-4
In reply to this post by Alban Hertroys-4
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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL search query is not executing in Postgres DB

Daniel Neugebauer
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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...