Hi Folks,

any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so

I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

thanks in advance!

could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?


rudy

Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

thanks anyway, Z

yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match

each of these requires some way of specifying the set of things that aren't there

you don't actually have to have an auxiliary table, though

try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something


rudy

darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack...

thanks,

zaphod

yeah, but i didn't know i was right at the time!

meanwhile, i had a look at the postgresql docs, and found this page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

rudy