Thread: MySQL search query is not executing in Postgres DB
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.
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
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
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?
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
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.
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.)
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
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.
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
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.