Re: Postgresql INET select and default route ?

Hi Albe,

Apologies for the delayed reply.

Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
query if you expect it to be returned by the second.
Is that an oversicht?

Thanks for the question, but no, it wasn't an oversight, I only am
looking for 0.0.0.0/0 to be returned if there is no more specific
match.

On 9 December 2015 at 12:45, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> Tim Smith wrote:
>> create table test(a inet);
>> insert into test values ('0.0.0.0/0');
>> insert into test values ('10.1.2.3');
>> => select * from test;
>> a
>> -----------
>> 0.0.0.0/0
>> 10.1.2.3
>> (2 rows)
>>
>>
>> This works as expected .....
>> => select * from test where a <<= '10.1.2.3';
>> a
>> ----------
>> 10.1.2.3
>> (1 row)
>>
>>
>> This does not work as expected ....
>> => select * from test where a <<= '11.1.2.3';
>> a
>> ---
>> (0 rows)
>>
>>
>> Surely the second one should return the default route entry (i.e. 0.0.0.0/0) ?
>
> I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
> query if you expect it to be returned by the second.
> Is that an oversicht?
>
> I guess your problem is a misunderstanding what the operator means:
>
> "<<=" is called "is contained by or equals".
> The definition (from the PostgreSQL source) is that x <<= y iff:
> - x has at least as many relevant bits as y and
> - all relevant bits of y are the same in x.
>
> So the following would produce what you expect:
>
> test=> select * from test where '11.1.2.3' <<= a;
> a
> -----------
> 0.0.0.0/0
> (1 row)
>
> Yours,
> Laurenz Albe