How to use LIKE on inet field in Postgres

postgresql sql-like inet
Aley picture Aley · Apr 24, 2013 · Viewed 10.4k times · Source

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.)

Answer

Jakub Kania picture Jakub Kania · Apr 24, 2013

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';

Related questions

PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 …

Read More
PostgreSQL: Select data with a like on timestamp field

I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error : SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone My request is : SELECT my_…

Read More
LIKE with % on column names

Here is my query that results in a syntax error: SELECT * FROM account_invoice,sale_order WHERE sale_order.name LIKE %account_invoice.origin% The account_invoice.origin field contains the text of sale_order.name, plus other text as …

Read More