Opened 12 years ago

Closed 9 years ago

#708 closed defect (fixed)

search for meta.IPAddressField with postgresql backend is broken (admin)

Reported by: jhernandez Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: normal Keywords: postgres
Cc: kilian.cavalotti@…, mattimustang@…, smileychris+django@… Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


In the admin interface the search for meta.IPAddressField with a postgresql backend is broken due to a missing cast to text from inet fields:

My model is like this:

class ConnectionType(meta.Model):
    name = meta.CharField(maxlength=20)

    def __repr__(self):

class Connection(meta.Model):
    connection_type = meta.ForeignKey(ConnectionType)
    source_ip = meta.IPAddressField()
    source_port = meta.PositiveIntegerField()
    destination_ip = meta.IPAddressField()
    destination_port = meta.PositiveIntegerField()
    state = meta.CharField(maxlength=20)

    def __repr__(self):
        return "%s:%d - %s:%d (%s)" % (self.source_ip, self.source_port, self.destination_ip, self.destination_port,

    class META:
        admin = meta.Admin(
            list_display = ('connection_type', 'source_ip', 'source_port', 'destination_ip', 'destination_port', 'state'),
            list_filter = ['connection_type'],
            search_fields = ['source_ip', 'destination_ip'],

in the admin inteface the search for an IPv4 address fails with the following traceback:

There's been an error:

Traceback (most recent call last):

  File "/usr/lib/python2.3/site-packages/django/core/handlers/", line 71, in get_response
    response = callback(request, **param_dict)

  File "/usr/lib/python2.3/site-packages/django/contrib/admin/views/", line 49, in _checklogin
    return view_func(request, *args, **kwargs)

  File "/usr/lib/python2.3/site-packages/django/contrib/admin/views/", line 180, in change_list
    result_count = p.hits

  File "/usr/lib/python2.3/site-packages/django/core/", line 67, in _get_hits
    self._hits = getattr(self.module, self.count_method)(**order_args)

  File "/usr/lib/python2.3/site-packages/django/utils/", line 3, in _curried
    return args[0](*(args[1:]+moreargs), **dict(kwargs.items() + morekwargs.items()))

  File "/usr/lib/python2.3/site-packages/django/core/meta/", line 1144, in function_get_count
    cursor.execute("SELECT COUNT(*)" + sql, params)

  File "/usr/lib/python2.3/site-packages/django/core/db/", line 10, in execute
    result = self.cursor.execute(sql, params)

ProgrammingError: ERROR:  operator does not exist: inet ~~* "unknown"
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

SELECT COUNT(*) FROM firewall_connections WHERE (firewall_connections.source_ip ILIKE '%10.10.1%' OR firewall_connections.destination_ip ILIKE '%10.10.1%')

In the following thread of the post to the pgsql-bugs mailing list is the complete explanation of this "bug":

on this message shows the proper query that should be used:

this is the main reason:

Attachments (1)

708-1.diff (660 bytes) - added by Matt McClanahan 10 years ago.

Download all attachments as: .zip

Change History (17)

comment:1 Changed 11 years ago by Adrian Holovaty

The correct SQL would be this:

select * from <table> where CAST(<field> as text) ilike <pattern>

In order to fix this, we would need the DB wrapper to have an extra per-field hook that would process values before using them in a LIKE statement. And on top of that, this would have to be database-engine-specific (only Postgres).

comment:2 Changed 11 years ago by Adrian Holovaty

#2149 was a duplicate.

comment:3 Changed 11 years ago by mattimustang@…

Cc: kilian.cavalotti@… mattimustang@… added
Component: Admin interfaceDatabase wrapper

A simple fix for this would be to make the IPAddressField a char(15) like every other backend instead of being a special case as it is now of being {inet.
In my own app I ended up using a CharField instead of IPAddressField because of this bug.

comment:4 Changed 10 years ago by (none)

milestone: Version 1.0

Milestone Version 1.0 deleted

comment:5 Changed 10 years ago by Chris Beaven

Cc: smileychris+django@… added
Triage Stage: UnreviewedAccepted

Matti, your idea sounds good. Want to provide a patch?

comment:6 Changed 10 years ago by Matthew Flanagan <mattimustang@…>

Has patch: set
Keywords: postgres added


This will be a backwards incompatible change for those users who are using postgres and IPAddressFields.

Index: django/db/backends/postgresql/
--- django/db/backends/postgresql/   (revision 4347)
+++ django/db/backends/postgresql/   (working copy)
@@ -14,7 +14,7 @@
     'FloatField':        'numeric(%(max_digits)s, %(decimal_places)s)',
     'ImageField':        'varchar(100)',
     'IntegerField':      'integer',
-    'IPAddressField':    'inet',
+    'IPAddressField':    'char(15)',
     'ManyToManyField':   None,
     'NullBooleanField':  'boolean',
     'OneToOneField':     'integer',

comment:7 Changed 10 years ago by Chris Beaven

Triage Stage: AcceptedReady for checkin

Thanks Matthew.

Core: I'm changing to "ready" for now. If you want more discussion, switch back to "design decision needed"

comment:8 Changed 10 years ago by Jacob

Triage Stage: Ready for checkinDesign decision needed

I'm setting this back to design decision needed since I'm not sure how to fix this exactly. We could:

# Disallow IP fields from searching
# Switch to char(15)
# Add casting

I'm not sure which is the right choice.

comment:9 Changed 10 years ago by Matt McClanahan

Unless I'm missing something, implementing the field and backend specific cast has become a lot easier than when the ticket was opened. This would surely be better than dropping support for the inet field type.

Changed 10 years ago by Matt McClanahan

Attachment: 708-1.diff added

comment:10 Changed 9 years ago by Jacob

Keywords: qs-rf added
Version: SVN

comment:11 Changed 9 years ago by Malcolm Tredinnick

Keywords: qs-rf removed

comment:12 Changed 9 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(In [7151]) Fixed #708 -- Fixed searching within IP fields on PostgreSQL.
Based on a patch from Matt McClanahan.

comment:13 Changed 9 years ago by Malcolm Tredinnick

The commit in [7151] isn't the perfect solution, since it does the cast always, but it retains backwards-compatibility and fixes the problem in a reasonable fashion (we don't support field-vs-field comparisons yet). It'll do for now.

comment:14 in reply to:  13 Changed 9 years ago by Thomas Adamcik

As I mentioned on the django-developer list I would recommend using "host(ip)" instead of "CAST(ip AS text)" as the cast will return the ip as an inet address, eg., while the host function will return the ip part.

comment:15 Changed 9 years ago by Jacob

Resolution: fixed
Status: closedreopened

I've reverted [7151] (in [7160]) while we figure this out more completely; see the django-dev thread for more.

comment:16 Changed 9 years ago by Jacob

Resolution: fixed
Status: reopenedclosed

(In [7161]) Re-enable substring lookups for IP address fields in Postgres using HOST() Thanks for the suggestion, Thomas Adamcik. Fixes #708.

Note: See TracTickets for help on using tickets.
Back to Top