Re: operator '~~' for types 'inet' and '"unknown"'

From: Antti Haapala <haapala(at)mail(dot)student(dot)oulu(dot)fi>
To: Shaun Jurrens <shaun(dot)jurrens(at)skoleetaten(dot)oslo(dot)no>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: operator '~~' for types 'inet' and '"unknown"'
Date: 2003-04-07 17:50:59
Message-ID: Pine.GSO.4.44.0304072034090.14110-100000@paju.oulu.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


On Mon, 7 Apr 2003, Shaun Jurrens wrote:

> I seem to have a problem that I haven't found an obvious answer to or a
> reference to in the mailing lists or on google. Using dumps on a little
> network database that I've made, I've run into problems with select's
> like:
>
> select * from ske_net_allocs where ip_net like '139.165.19%';
>
> These work on 7.2.3 on freebsd but not with
>
> PostgreSQL 7.3.2 on i386-portbld-freebsd4.7.
>
> I get the following error:
>
> ERROR: Unable to identify an operator '~~' for types 'inet' and '"unknown"'
> You will have to retype this query using an explicit cast
>
> This may be a real bonehead question, but I'm unfortunately too ignorant
> to answer it yet. I've tried using dumps from the 7.2.3 box using both
> pg_dump and pg_dump -o. Thanks in advance for any hints/clue bats.
>
> I'd appreciate a CC, because I read the list via the archives. Thanks.

Some implicit casting rules have been dropped as of PG 7.3 and so this
problem has nothing to do with pg_dump options ;)

To use "like" with "inet" you need to cast it explicitly to "text", so try

select * from ske_net_allocs where ip_net::text like '139.165.19%';

Of course no normal index on ip_net would help you with this...

This could also work (untested)

select * from ske_net_allocs where ip_net >= '139.165.190.0' and
ip_net <= '139.165.199.255';

And it perhaps even uses indices.

See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-net.html
for complete list of "inet" operators.

--
Antti Haapala

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2003-04-07 18:25:36 Re: Basic info
Previous Message Alessandro Galasso 2003-04-07 15:53:36 Re: Basic info