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!