Thread: operator '~~' for types 'inet' and '"unknown"'
Hi, 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. -- Med vennlig hilsen/Sincerely, Shaun D. Jurrens Drift og Sikkerhetskonsulent IKT-Avdeling Oslo Skoleetaten Tel: +47 2208 7394 Mobil: +47 9820 8826 gpg key fingerprint: 007A B6BD 8B1B BAB9 C583 2D19 3A7F 4A3E F83E 84AE
Attachment
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