On Tue, Jul 9, 2013 at 7:12 PM, Josh Berkus <j...@agliodbs.com> wrote: > Like uses the operator class "text_pattern_ops" which doesn't include an > implict cast.
This is wrong. Casts are associated with data types, not operator classes. > For one thing, the implicit cast is from text --> > integer, not the other way around, and there is no LIKE operator for > integers. This is also wrong. There's no implicit cast between text and integer in either direction - only assignment casts. I think the reason why the first few examples work and the last one fails is that, in the first few cases, there are integer literals and unknown literals, and the operations in question are defined for integers (and for text), so we pick the integer interpretation and call it good. But in the non-working case, the operation is defined to work only on text, and an integer argument is supplied. So we have to cast, and there's no implicit cast, ergo we fail. The point is that we use a different procedure to decide what to do with a quoted literal ('1', or '3.14159', or 'foo') than we do to decide whether it's OK to cast a value of a type we already know. For example this fails: rhaas=# create table foo (a int, b varchar); CREATE TABLE rhaas=# insert into foo values ('1', '1'); INSERT 0 1 rhaas=# select * from foo where a = b; ERROR: operator does not exist: integer = character varying LINE 1: select * from foo where a = b; ^ This is just like the OP's first example (which worked) except that here there's no unknown literal, so we actually need to cast, and we refuse to do so and fail. I continue to be of the opinion that our behavior in this area is bone-headed. It's reasonable to reject integer = character varying on the basis that we don't know whether integer or character varying comparison semantics are wanted, and the two might give different answers (think: leading zeroes), so we'd better ask the user to clarify. But '1' LIKE 1 is not ambiguous; there is only one plausible meaning for that, and we ought to adopt it, per the patch I proposed previously. Rejecting SQL that other systems happily accept is unhelpful and unfriendly and it is costing us users and mind-share. </rant> -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers