Greater than for INET IP address field in PostgreSQL Version > 9

So I have wrote my own lookups for PostgreSQL INET field in my DRF App as Django does not natively support those PostgreSQL specific fields. I use my own IP field in my objects to support my own lookups.

All my lookup work find, except for those two:

class HostGreaterOrEqual(Lookup):
    """ Lookup to check if an IP address is greater than or equal. Used for net ranges"""
    lookup_name = "host_greater_or_equal"

    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return "%s >= %s" % (lhs, rhs), params
    
class HostLessOrEqual(Lookup):
    """ Lookup to check if an IP address is less than or equal. Used for net ranges"""
    lookup_name = "host_less_or_equal"

    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return "%s <= %s" % (lhs, rhs), params

In my models I have a address and address_end field to store IP Ranges. I wanted to use those lookups above to make the proper queries like this:

items = queryset.filter(
                Q(address__host_greater_or_equal=value)
                & Q(address_end__host_less_or_equal=value)
            )

In the documentation for PostgreSQL Version 9 I can see the operators for "is lass or equal" and "is greater or equal". But if I check the documentation for Version 15 it seems like those have been removed.

Was that functionality simply removed or is there something similar that I have not found yet?

From the v13.0 release notes:

Reformat tables containing function and operator information for better clarity (Tom Lane)

So they aren't in their usual place in the documentation since then, but they're still available.

select inet '192.168.1.5' <  inet '192.168.1.6' as "less than",
       inet '192.168.1.5' >  inet '192.168.1.6' as "greater than",
       inet '192.168.1.5' <= inet '192.168.1.6' as "less than or equal",
       inet '192.168.1.5' >= inet '192.168.1.6' as "greater than or equal";
-- less than | greater than | less than or equal | greater than or equal
-------------+--------------+--------------------+-----------------------
-- t         | f            | t                  | f

show server_version;
--          server_version
-------------------------------------
-- 15rc2 (Debian 15~rc2-1.pgdg110+1)

Which you can test on different versions here: online demo.

You can always check the system tables to make sure what's really available

select * 
from pg_catalog.pg_operator 
where oprname in ('<=','>=') 
and oprcode::text ilike '%network%';
-- oid  | oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate |  oprcode   |   oprrest   |     oprjoin
--------+---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+------------+-------------+-----------------
-- 1204 | <=      |           11 |       10 | b       | f           | f          |     869 |      869 |        16 |   1206 |      1205 | network_le | scalarlesel | scalarlejoinsel
-- 1206 | >=      |           11 |       10 | b       | f           | f          |     869 |      869 |        16 |   1204 |      1203 | network_ge | scalargesel | scalargejoinsel

Or look them up in psql with \do+

\do+ <= inet inet
--                                         List of operators
--   Schema   | Name | Left arg type | Right arg type | Result type |  Function  |    Description
--------------+------+---------------+----------------+-------------+------------+--------------------
-- pg_catalog | <=   | inet          | inet           | boolean     | network_le | less than or equal

\do+ >= inet inet
--                                           List of operators
--   Schema   | Name | Left arg type | Right arg type | Result type |  Function  |      Description
--------------+------+---------------+----------------+-------------+------------+-----------------------
-- pg_catalog | >=   | inet          | inet           | boolean     | network_ge | greater than or equal

psql -E shows \do+ finds it using the following query, that you can issue in a different client.

SELECT n.nspname as "Schema",
  o.oprname AS "Name",
  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS "Left arg type",
  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
  pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
  o.oprcode AS "Function",
  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),
           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
     LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft
     LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace
     LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright
     LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace
WHERE o.oprname OPERATOR(pg_catalog.~) '^(>=)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_operator_is_visible(o.oid)
  AND (t0.typname OPERATOR(pg_catalog.~) '^(inet)$' COLLATE pg_catalog.default
        OR pg_catalog.format_type(t0.oid, NULL) OPERATOR(pg_catalog.~) '^(inet)$' COLLATE pg_catalog.default)
  AND pg_catalog.pg_type_is_visible(t0.oid)
  AND (t1.typname OPERATOR(pg_catalog.~) '^(inet)$' COLLATE pg_catalog.default
        OR pg_catalog.format_type(t1.oid, NULL) OPERATOR(pg_catalog.~) '^(inet)$' COLLATE pg_catalog.default)
  AND pg_catalog.pg_type_is_visible(t1.oid)
ORDER BY 1, 2, 3, 4;
Back to Top