A question on using CIDR datatype for both ipv6 and ipv4 address

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I need some help in understanding why this is not working:

I have created a cidr_test table with datatypes cidr, varchar and bigint.,

rwdb=# \d cidr_test;
             Table "public.cidr_test"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 ip_as_cidr   | cidr                  |
 ip_as_text   | character varying(40) |
 ip_as_number | bigint                |

And populated the table with the following values:

rwdb=# select * from cidr_test;
       ip_as_cidr       |     ip_as_text     | ip_as_number
------------------------+--------------------+--------------
 ::ffff:192.0.2.128/128 | ::ffff:192.0.2.128 |   3221226112
 ::ffff:192.0.2.125/128 | ::ffff:192.0.2.125 |   3221226109
 ::ffff:192.0.2.126/128 | ::ffff:192.0.2.126 |   3221226110
 ::ffff:192.0.2.127/128 | ::ffff:192.0.2.127 |   3221226111
 192.0.2.124/32         | 192.0.2.124        |   3221226108
(5 rows)


Note that the 5th row, is an IPV4 address in IPV4 format unlike others
which are in IPV6 format.

When I run the following query:

select * from cidr_test where inet(ip_as_cidr) >= inet '::ffff:192.0.2.124/128';

       ip_as_cidr       |     ip_as_text     | ip_as_number
------------------------+--------------------+--------------
 ::ffff:192.0.2.128/128 | ::ffff:192.0.2.128 |   3221226112
 ::ffff:192.0.2.125/128 | ::ffff:192.0.2.125 |   3221226109
 ::ffff:192.0.2.126/128 | ::ffff:192.0.2.126 |   3221226110
 ::ffff:192.0.2.127/128 | ::ffff:192.0.2.127 |   3221226111
(4 rows)

The results are different from the following:

select * from cidr_test where inet(ip_as_cidr) >= inet '192.0.2.124/32';

       ip_as_cidr       |     ip_as_text     | ip_as_number
------------------------+--------------------+--------------
 ::ffff:192.0.2.128/128 | ::ffff:192.0.2.128 |   3221226112
 ::ffff:192.0.2.125/128 | ::ffff:192.0.2.125 |   3221226109
 ::ffff:192.0.2.126/128 | ::ffff:192.0.2.126 |   3221226110
 ::ffff:192.0.2.127/128 | ::ffff:192.0.2.127 |   3221226111
 192.0.2.124/32         | 192.0.2.124        |   3221226108
(5 rows)


Let me know why I am not getting similar results when the RHS is an
IPV4 in IPV6 format vs, the RHS in IPV4 format.

Thanks in advance!

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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux