Re: [GENERAL] Postgresql INET select and default route ?

[Tim Smith]

Fabuous !  Thank you !

On 14 December 2015 at 07:52, Albe Laurenz  wrote:
> Tim Smith wrote:
>> 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.
>
> I see, but then you'll have to use a different query:
>
> SELECT a from test where '11.1.2.3' <<= a
>ORDER BY masklen(a) DESC LIMIT 1;
>
> Yours,
> Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql INET select and default route ?

[Tim Smith]

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  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql INET select and default route ?

[Albe Laurenz]

Tim Smith wrote:
> 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.

I see, but then you'll have to use a different query:

SELECT a from test where '11.1.2.3' <<= a
   ORDER BY masklen(a) DESC LIMIT 1;

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql INET select and default route ?

[Albe Laurenz]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgresql INET select and default route ?

[Tim Smith]

Hi,

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)  ?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general