Postgresql INET select and default route ?
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
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
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.albe@wien.gv.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
Fabuous ! Thank you !
On 14 December 2015 at 07:52, Albe Laurenz <laurenz.albe@wien.gv.at> 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