X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 44E05D1C976
	for <pgsql-bugs-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Nov 2003 07:55:02 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 56265-06
	for <pgsql-bugs-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Nov 2003 03:54:32 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 96948D1D884
	for <pgsql-bugs@postgresql.org>; Sun, 23 Nov 2003 03:54:30 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 6C74935223; Sat, 22 Nov 2003 23:54:16 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 6AEBD350D5; Sat, 22 Nov 2003 23:54:16 -0800 (PST)
Date: Sat, 22 Nov 2003 23:54:16 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Douglas M. Westfall" <dougw@net.kent.edu>
Cc: pgsql-bugs@postgresql.org
Subject: Re: Bug Report
In-Reply-To: <1069559998.8098.2.camel@dw2>
Message-ID: <20031122235221.H37295@megazone.bigpanda.com>
References: <1069387744.2491.1.camel@dw2> <1069559998.8098.2.camel@dw2>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/168
X-Sequence-Number: 7211

On Sat, 22 Nov 2003, Douglas M. Westfall wrote:

> Yes, I have searched the archives, and searches for 'inet' or 'ilike' or
> 'inet+ilike' and 'inet&ilike' return 0 results each.
>
> Have I missed something?

I believe your problem is that you were relying on a cast from inet to
text (on which ilike is defined) which isn't marked as implicit,
you'd probably want:
select * from <table> where CAST(<field> as text) ilike <pattern>;


> > Please enter a FULL description of your problem:
> > ------------------------------------------------
> > 1) table contains a valid inet field
> > 2) query works fine in pgsql 7.2 & 7.3
> > 3) query no longer works in pgsql 7.4
> >
> > select * from <table> where <field> ilike '%<inetvalue>%' ;
> >
> > fails with:
> > ERROR:  Unable to identify an operator '~~*' for types 'inet' and
> > '"unknown"'
> >         You will have to retype this query using an explicit cast
> >
> >
> > Please describe a way to repeat the problem.   Please try to provide a
> > concise reproducible example, if at all possible:
> > ----------------------------------------------------------------------
> >
> > select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%' order by
> > ip2_address asc;
> >
> > CASTing the operator provides no resolution, as it causes syntax errors,
> > ie:
> > select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%'::inet
> > order by ip2_address asc;
> > returns:
> > ERROR:  invalid INET value '%10.123.252.4%'
> > AND:
> > select * from ipaddrs2 where ip2_address ilike CAST('%10.123.252.4% as
> > inet) order by ip2_address asc;
> > returns:
> > ERROR:  invalid INET value '%10.123.252.4%'