MySQL search query is not executing in Postgres DB

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

Hi,

Thanks for the reply. It is not possible to change the query in our
environment. So we need to do casting. I'm a new bee, so it will be more
helpful if you give me some simple examples.

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

On 02/17/2012 04:22 AM, premanand wrote:

Hi,

Thanks for the reply. It is not possible to change the query in our
environment. So we need to do casting. I'm a new bee, so it will be more
helpful if you give me some simple examples.

That's not what this list is for, You should be asking in pgsql-general,
not here, (so don't just reply to this email). But in any case, you
should take Heikki's advice seriously. What you are doing is broken. You
should fix it and not just say "Oh, we can't".

cheers

andrew

On Fri, Feb 17, 2012 at 2:45 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

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.

Why?

I understand why it's a bad idea to rely on integer = text doing
anything sane - is that text equality or numeric equality?

And in theory the same issue could exist here if there were another
meaning for LIKE. But there isn't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

I understand why it's a bad idea to rely on integer = text doing
anything sane - is that text equality or numeric equality?

And in theory the same issue could exist here if there were another
meaning for LIKE. But there isn't.

Really? LIKE is just a different spelling for operator ~~, and there is
nothing stopping people from defining more operators named that, not to
mention that there are already four of them in core PG. In particular
the bytea ~~ bytea version is explicitly intended to provide a LIKE
implementation for non-text types. I see some operators named ~~ in
contrib as well.

regards, tom lane

On Fri, Feb 17, 2012 at 10:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I understand why it's a bad idea to rely on integer = text doing
anything sane - is that text equality or numeric equality?

And in theory the same issue could exist here if there were another
meaning for LIKE.  But there isn't.

Really?  LIKE is just a different spelling for operator ~~, and there is
nothing stopping people from defining more operators named that, not to
mention that there are already four of them in core PG.  In particular
the bytea ~~ bytea version is explicitly intended to provide a LIKE
implementation for non-text types.  I see some operators named ~~ in
contrib as well.

As far as I know, the fact that LIKE is another spelling for ~~ is a
PostgreSQL implementation detail with which users ought not to be
burdened. But even given that, there are many situations where we
currently complain about ambiguity even though the ambiguity is
entirely hypothetical: there COULD be a ~~(int,text) operator, but
there actually isn't. Now, I'll admit that this is not an easy
problem to solve without giving up something somewhere, since it's
clearly undesirable for the meaning of something that worked before to
silently change when and if someone defines a new operator.

But on the other hand, I think that labeling the user's coding
practices as sloppy is a cop-out. There are many, many people running
on not only MySQL but also on Oracle who have written large amounts of
code that requires fewer type casts on those systems than it does on
PostgreSQL. That fact presents serious migration challenges for such
users when they move over to PostgreSQL. Labeling the code as the
problem excuses us from the need to think about how to make our type
system work any better than it does today. Boo, hiss. If we're not
doing anything about this because we have carefully examined the
subject and decided that this is a trade-off we must make because
MySQL or Oracle doesn't support XYZ and we do, then let's give that
explanation to the user instead of telling them the problem is that
their code stinks. Otherwise, we have some soul-searching to do, as
time permits.

I remember there was a time when you couldn't say "SELECT a x FROM
foo" in PostgreSQL. We told people that it was because our syntax was
more flexible - we have postfix operators, or something. I no longer
remember the details of where the grammar conflict came from. But
somebody (probably you or Hiroshi Saito, judging by the commit log)
figured out a way to get around the problem, and now that syntax works
fine in 99% of the cases people care about. That is a huge usability
improvement for people coming from other database systems where AS was
never required. I don't know whether a similar improvement is
possible in this area, but we're certainly not going to get there by
labeling the user's expectations as unreasonable. I don't think they
are, and the people who wrote MySQL and Oracle evidently agree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

I remember there was a time when you couldn't say "SELECT a x FROM
foo" in PostgreSQL. We told people that it was because our syntax was
more flexible - we have postfix operators, or something.

Which it was, and yes that was the reason. We eventually thought of a
kluge solution that lets you omit "AS" 90% of the time, which is better
than nothing; but I doubt it would ever have been accepted if it weren't
a matter of improving standards compliance. I am pretty sure that the
SQL spec doesn't say that you should be able to apply LIKE directly to
an integer, so that issue isn't comparable to this one.

I don't know whether a similar improvement is
possible in this area, but we're certainly not going to get there by
labeling the user's expectations as unreasonable. I don't think they
are, and the people who wrote MySQL and Oracle evidently agree.

The people who wrote MySQL had very poor taste in a lot of areas, and
we are not going to blindly follow their lead. Oracle is not a terribly
presentable system either. Having said that, I don't object to any
clean improvements we can think of in this area --- but "make it work
more like MySQL" had better not be the only argument for it.

regards, tom lane

Robert Haas <robertmhaas@gmail.com> wrote:

As far as I know, the fact that LIKE is another spelling for ~~ is

a

PostgreSQL implementation detail with which users ought not to be
burdened.

+1

LIKE is well defined by the standard, and the ~~ operator is not
mentioned there anywhere.

On the other hand, LIKE is defined to either work on character
strings or binary strings -- there is nothing in the standard about
using it with other data types or automatic casting to support that.
Any such support would be a non-standard PostgreSQL extension. As
such, anyone wanting to write portable code should avoid that by
explicit casting (which should be portable).

I remember there was a time when you couldn't say "SELECT a x FROM
foo" in PostgreSQL.

That was in violation of the SQL standard, which makes AS an allowed
but optional noise word.

In spite of all that, perhaps we should have a compatibility
extension which provides more casts, in an attempt to ease the
transition from other databases? Personally, I like having the
default behavior this strict -- I think it reduces the chance of
errors, reduces the chances of accidentally having type mismatches
which defeat optimizations, and improves portability. But I have
nothing against allowing someone to give all that up to ease
transition from another product. My biggest concern is whether we
might "paint ourselves into a corner" by including such an
extension. It might shut off avenues for other cool features
because anyone using the extension would have conflicts. Perhaps
such a thing would be more appropriate on PGXN with admonitions that
it was only intended to ease conversion and that users were
encouraged to migrate to standard syntax as soon as possible.

-Kevin

2012/2/17 Kevin Grittner <Kevin.Grittner@wicourts.gov>:

Robert Haas <robertmhaas@gmail.com> wrote:

As far as I know, the fact that LIKE is another spelling for ~~ is

a

PostgreSQL implementation detail with which users ought not to be
burdened.

+1

LIKE is well defined by the standard, and the ~~ operator is not
mentioned there anywhere.

On the other hand, LIKE is defined to either work on character
strings or binary strings -- there is nothing in the standard about
using it with other data types or automatic casting to support that.
Any such support would be a non-standard PostgreSQL extension. As
such, anyone wanting to write portable code should avoid that by
explicit casting (which should be portable).

I remember there was a time when you couldn't say "SELECT a x FROM
foo" in PostgreSQL.

That was in violation of the SQL standard, which makes AS an allowed
but optional noise word.

In spite of all that, perhaps we should have a compatibility
extension which provides more casts, in an attempt to ease the
transition from other databases?  Personally, I like having the
default behavior this strict -- I think it reduces the chance of
errors, reduces the chances of accidentally having type mismatches
which defeat optimizations, and improves portability.  But I have
nothing against allowing someone to give all that up to ease
transition from another product.  My biggest concern is whether we
might "paint ourselves into a corner" by including such an
extension.  It might shut off avenues for other cool features
because anyone using the extension would have conflicts.  Perhaps
such a thing would be more appropriate on PGXN with admonitions that
it was only intended to ease conversion and that users were
encouraged to migrate to standard syntax as soon as possible.

+1

Pavel

On Fri, Feb 17, 2012 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I don't know whether a similar improvement is
possible in this area, but we're certainly not going to get there by
labeling the user's expectations as unreasonable.  I don't think they
are, and the people who wrote MySQL and Oracle evidently agree.

The people who wrote MySQL had very poor taste in a lot of areas, and
we are not going to blindly follow their lead.  Oracle is not a terribly
presentable system either.  Having said that, I don't object to any
clean improvements we can think of in this area --- but "make it work
more like MySQL" had better not be the only argument for it.

Hey, if I preferred MySQL to PostgreSQL, I wouldn't be here. That
doesn't mean that there are exactly 0 things that they do better than
we do. What I'm unhappy about isn't that we're not bug-compatible
with MySQL, but rather that, in this case, I like MySQL's behavior
better, and the fact that they've made it work means it's not
theoretically impossible. It just involves some trade-off that I
don't believe we've thought about hard enough.

Standards compliance is a means to an end. The purpose of having
standards is to allow for interoperable implementations of the same
underlying functionality. That doesn't mean we should copy
non-standard warts, of course, but it isn't obvious to me that this is
a wart. No one has suggested that the user's actual query has more
than one reasonable interpretation, so complaining that it's ambiguous
doesn't impress me very much.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/17/2012 12:59 PM, Robert Haas wrote:

On Fri, Feb 17, 2012 at 12:14 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

I don't know whether a similar improvement is
possible in this area, but we're certainly not going to get there by
labeling the user's expectations as unreasonable. I don't think they
are, and the people who wrote MySQL and Oracle evidently agree.

The people who wrote MySQL had very poor taste in a lot of areas, and
we are not going to blindly follow their lead. Oracle is not a terribly
presentable system either. Having said that, I don't object to any
clean improvements we can think of in this area --- but "make it work
more like MySQL" had better not be the only argument for it.

Hey, if I preferred MySQL to PostgreSQL, I wouldn't be here. That
doesn't mean that there are exactly 0 things that they do better than
we do. What I'm unhappy about isn't that we're not bug-compatible
with MySQL, but rather that, in this case, I like MySQL's behavior
better, and the fact that they've made it work means it's not
theoretically impossible. It just involves some trade-off that I
don't believe we've thought about hard enough.

Standards compliance is a means to an end. The purpose of having
standards is to allow for interoperable implementations of the same
underlying functionality. That doesn't mean we should copy
non-standard warts, of course, but it isn't obvious to me that this is
a wart. No one has suggested that the user's actual query has more
than one reasonable interpretation, so complaining that it's ambiguous
doesn't impress me very much.

Assuming we had the cast, What would "intval like '1%'" mean? You're
going to match 1, 10..19, 100..199, 1000..1999 ...

Now maybe there's a good use for such a test, but I'm have a VERY hard
time imagining what it might be.

cheers

andrew

Greetings,

My biggest concern is whether we
might "paint ourselves into a corner" by including such an
extension. It might shut off avenues for other cool features
because anyone using the extension would have conflicts. Perhaps
such a thing would be more appropriate on PGXN with admonitions that
it was only intended to ease conversion and that users were
encouraged to migrate to standard syntax as soon as possible.

IMHO if you give someone syntax surgar like this and tell them to "fix it ASAP"
it will never get fixed properly. I'm all for getting new users to pgsql, but
this is not the way to do it.

Regards,
J

On Fri, Feb 17, 2012 at 1:21 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Assuming we had the cast, What would "intval like '1%'" mean? You're going
to match 1, 10..19, 100..199, 1000..1999 ...

Now maybe there's a good use for such a test, but I'm have a VERY hard time
imagining what it might be.

Well, I can readily see someone encoding parts of their application
into this sort of encoding, so that, for instance, all customer
numbers beginning with "1" are deemed to be "internal accounts."

It's a pretty terrible approach to encoding data for an application;
it leads to stuff like the "oops, once they have generated 30,000
invoices, the system reaches doomsday and can't work anymore."
http://thedailywtf.com/Articles/Ive-Got-Your-Number.aspx

But nothing prevents users from designing their applications to encode
information in their ID prefixes.

And I have *zero* confidence that for PostgreSQL to rule out "LIKE
'1%'" is preventing those designs from getting built...

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Feb 17, 2012, at 11:28 AM, Christopher Browne wrote:

And I have *zero* confidence that for PostgreSQL to rule out "LIKE
'1%'" is preventing those designs from getting built...

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

He would turn to Tonto, who undoubtably would advise:

If you want to treat an integer like a string, figure out how to convert your integer into a string …

The original query strikes me as being similar to expecting the Lone Ranger's six-shooter to be capable of shooting Tonto's arrows.

----
Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org

On 02/17/2012 02:28 PM, Christopher Browne wrote:

On Fri, Feb 17, 2012 at 1:21 PM, Andrew Dunstan<andrew@dunslane.net> wrote:

Assuming we had the cast, What would "intval like '1%'" mean? You're going
to match 1, 10..19, 100..199, 1000..1999 ...

Now maybe there's a good use for such a test, but I'm have a VERY hard time
imagining what it might be.

Well, I can readily see someone encoding parts of their application
into this sort of encoding, so that, for instance, all customer
numbers beginning with "1" are deemed to be "internal accounts."

It's a pretty terrible approach to encoding data for an application;
it leads to stuff like the "oops, once they have generated 30,000
invoices, the system reaches doomsday and can't work anymore."
http://thedailywtf.com/Articles/Ive-Got-Your-Number.aspx

But nothing prevents users from designing their applications to encode
information in their ID prefixes.

And I have *zero* confidence that for PostgreSQL to rule out "LIKE
'1%'" is preventing those designs from getting built...

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Strings of digits used that way should not be stored in numeric fields
at all, IMNSHO, just as zip codes and phone numbers should not be. They
should be text in the first place, and if the OP had done that he
wouldn't have had any difficulty about this. I hope that's what the Lone
Ranger would do ...

cheers

andrew

On Fri, Feb 17, 2012 at 1:21 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Assuming we had the cast, What would "intval like '1%'" mean? You're going
to match 1, 10..19, 100..199, 1000..1999 ...

Yep.

Now maybe there's a good use for such a test, but I'm have a VERY hard time
imagining what it might be.

Dunno. Presumably the test is meaningful for the OP's IDs, or he
wouldn't have written the query that way.

The time I got bitten by this was actually with LPAD(), rather than
LIKE. I had a serial column which I wanted to use to generate record
identifiers off of a sequence: B00001, B00002, B00003, B00004, etc.
So I wrote 'B' || lpad(id, 5, '0'). When the implicit casting
changes came along, I had to go back and change that to id::text.
Fortunately that wasn't a lot of work, especially since by that time I
was following pgsql-hackers enough to understand immediately why it
broke, but it did and does seem unnecessary, because there is no real
ambiguity there. Yeah, there could be ambiguity, if someone created
another LPAD() function... but no one did.

Here's yet another case where the current rules are thoroughly disagreeable.

rhaas=# create or replace function z(smallint) returns smallint as
$$select $1+1$$ language sql;
ERROR: return type mismatch in function declared to return smallint
DETAIL: Actual return type is integer.
CONTEXT: SQL function "z"

So cast the result from an integer to a smallint. What's the big deal?

But, OK, I'll do it your way:

rhaas=# create or replace function z(smallint) returns smallint as
$$select $1+1::smallint$$ language sql;
CREATE FUNCTION
rhaas=# select z(1);
ERROR: function z(integer) does not exist
LINE 1: select z(1);
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

Come on, really? Note that the above example works without casts if
you use int *or* bigint *or* numeric, but not smallint. That could be
fixed by causing sufficiently-small integers to lex as smallints, but
if you think implicit datatype coercions are evil, you ought to be
outraged by the fact that we are already going out of our way to blur
the line between int, bigint, and numeric. We let people write 2.0 +
3 and get 5.0 - surely it's only a short step from there to human
sacrifice, cats and dogs living together... mass hysteria! I mean,
the whole reason for rejecting integer = text is that we aren't sure
whether to coerce the text to an integer or the integer to a string,
and it's better to throw an error than to guess. But in the case of
2.0 + 3, we feel 100% confident in predicting that the user will be
happy to convert the integer to a numeric rather than the numeric to
an integer, so no error. We do that because we know that the domain
of numeric is a superset of the domain of integer, or in other words,
we are using context clues to deduce what the user probably meant
rather than forcing them to be explicit about it.

And yet in other cases, such as LIKE or LPAD with an integer rather
than a string, or just about anything involving smallint, the user is
required to be explicit, even though in most cases there is only one
reasonable implementation of the query. What is the value in erroring
out on a query that's not actually ambiguous? Numerous people here
are defending that behavior as if it were somehow morally superior,
but I think it's merely an accident of how the post-8.3 type system
happens to work. On pre-8.3 systems this all works differently, and
some of those old behaviors are worse than what we have now, while
others are better. If we really believed that implicit casts any form
were evil, we would have removed them entirely instead of trimming
them back. I don't see why it's heretical to suggest that the 8.3
casting changes brought us to exactly that point in the universe where
everything is perfect and nothing can be further improved; does anyone
seriously believe that?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Andrew Dunstan <andrew@dunslane.net> writes:

Assuming we had the cast, What would "intval like '1%'" mean? You're
going to match 1, 10..19, 100..199, 1000..1999 ...

Now maybe there's a good use for such a test, but I'm have a VERY hard
time imagining what it might be.

Yeah, that's another point worth asking. Coercing an integer to text
and then doing LIKE on it is an extremely inefficient way to do what's
probably the wrong thing anyhow. I would be interested to know exactly
why the OP wants to do this, and whether it couldn't be done better with
some arithmetical test.

In this connection it's worth remembering that when we removed some
implicit casts in 8.3, we heard lots of yelling, and we also heard from
lots of people who found bugs in their SQL code that the implicit casts
had masked. Allowing LIKE-on-anything could be a rerun of that mess.

regards, tom lane

On Fri, Feb 17, 2012 at 2:44 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Strings of digits used that way should not be stored in numeric fields at
all, IMNSHO, just as zip codes and phone numbers should not be. They should
be text in the first place, and if the OP had done that he wouldn't have had
any difficulty about this. I hope that's what the Lone Ranger would do ...

The argument isn't about whether the user made the right design
choices; it's about whether he should be forced to insert an explicit
type cast to get the query to do what it is unambiguously intended to
do. I don't believe it's entirely self-evident that it's always
better to store strings of integers in a text column rather than as an
integer or bigint - integers are pretty fast and compact. Even
granting that a text field would have been better, nobody's arguing
that you can't do LIKE against an integer column; we're just
discussing what syntax is required to make the user's intent
acceptably clear.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/17/2012 02:52 PM, Robert Haas wrote:

If we really believed that implicit casts any form were evil, we would
have removed them entirely instead of trimming them back. I don't see
why it's heretical to suggest that the 8.3 casting changes brought us
to exactly that point in the universe where everything is perfect and
nothing can be further improved; does anyone seriously believe that?

I don't believe we are necessarily at a perfect place, nor have I said
it, nor has anyone else that I'm aware of. Neither am I opposed to
implementing MySQL features (or doing them better) when appropriate.

But I do believe that a test for "intval like '1%'" is very likely to
come from a broken design.

cheers

andrew