Why isn't this PosgreSQL LIKE query working?

Question

PostgreSQL 9.2.

[local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%';
ERROR:  42883: operator does not exist: inet ~~ unknown
 LINE 1: select * from units where ip_address LIKE '192.168.43.%';
                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:722
Time: 0.309 ms

What am I doing wrong?

Answer 1

The error message clearly describes the problem: "operator does not exist: inet ~~ unknown"

So you should use the native operators for inet type.

In your case it should be

... where ip_address << '192.168.43/24'::inet;

where the value after the slash specifies the number of significant bits in the value (24 bits = 3 bytes = x.y.z.any). For example 192.168.128/17 means any address from 192.168.128.0 to 192.168.255.255.

---

You can convert an inet value into varchar and deal with it like with any other varchar but it will break any benefits from the original type and disallow indexes if any. However, here is example:

... where ip_address::varchar like '192.168.43.%/32';

Additionally it may provide the wrong result if ip_address value is partially qualified in the way mentioned above (for example if ip_address = '192.168/16' it is not like '192.168.1.%' which is generally wrong because '192.168/16' value holds anything from '192.168.0.0' to '192.168.255.255')