pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem using IP functions
2+ messages / 2 participants
[nested] [flat]

* Problem using IP functions
@ 2001-05-11 20:33 Marc Lamothe <mlamothe@openface.ca>
  2001-05-13 03:44 ` Re: Problem using IP functions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Marc Lamothe @ 2001-05-11 20:33 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I'm having trouble using the host() and netmask() functions within a select
query.  For some reason, the following query returns 1 row:

ipdb=> select id, subnet_number from subnet where subnet_number =
'216.46.13.0';
 id | subnet_number
----+---------------
 96 | 216.46.13.0
(1 row)

Yet, if I replace the ip string literal with host('216.46.13.0/24'), I get
no results.  ie:

ipdb=> select id, subnet_number from subnet where subnet_number =
host('216.46.13.0/24');

 id | subnet_number
----+---------------
(0 rows)

Even though host('216.46.13.0/24') evaluates to 216.46.13.0

ipdb=> select host('216.46.13.0/24');
    host
-------------
 216.46.13.0
(1 row)

The subnet_number column is a varchar(16) which I assume you can compare
with a text data type, which is what host() returns.  Just to be sure, I
tried casting everything to type text, but that didn't do the trick.

ipdb=> select texteq(host('216.46.13.0/24')::text, '216.46.13.0'::text);
 texteq
--------
 f
(1 row)

Any insight would be greatly appreciated.

Marc





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem using IP functions
  2001-05-11 20:33 Problem using IP functions Marc Lamothe <mlamothe@openface.ca>
@ 2001-05-13 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-05-13 03:44 UTC (permalink / raw)
  To: Marc Lamothe <mlamothe@openface.ca>; +Cc: pgsql-sql

"Marc Lamothe" <mlamothe@openface.ca> writes:
> The subnet_number column is a varchar(16) which I assume you can compare
> with a text data type, which is what host() returns.

Are you on a pre-7.1 Postgres release?  host() is buggy before 7.1 ---
it includes a trailing null in its output, which it shouldn't oughta
have done.  You can't see the null from outside the system, but it
manages to mess up text comparisons anyway.

BTW, you should consider using inet or cidr datatype for that column
rather than varchar...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-05-13 03:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-11 20:33 Problem using IP functions Marc Lamothe <mlamothe@openface.ca>
2001-05-13 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox