How to use LIKE on inet field in Postgres

Question

I need to perform the following query:

SELECT * FROM users WHERE ip LIKE '88.99.%';

The problem is that the inet datatype does not seems to support wildcards (at least not in the way I have utilized them in the preceding query).

What I'd like to know is whether it would be a smart move to convert the field into string before specifying the LIKE filter? (On the other hand, this would probably slow down the whole query, even if the approach itself was valid.)

Webbed

Answer 1

Use subnet operators for such queries. '88.99.0.0/16' should do it :

SELECT * FROM users WHERE ip << inet '88.99.0.0/16';

Foretopsail

Thanks. Any idea of how to use this in ORMs like SQLAlchemy? – Voice

Answer 2

Another option which worked for me if you want to do a string comparison is this:

SELECT * FROM users WHERE TEXT(ip) LIKE '88.99.%';

although it will be less efficient than "How to use LIKE on inet field in Postgres".

Reference: https://www.postgresql.org/docs/8.2/static/functions-net.html

Raman

The datatype in this case is inet, try to avoid the cast of all data. – Weston

@FrankNStein, sometimes it's impossible to avoid. For example I want to search %.10.%, only this solution will work I suppose. – Sporulate

yep, that case is the worst scenario :( – Weston