Postgres inet array contains a single ip

Question

Requirement: user can add single ips or ip address ranges to a database table(for example permissions table having ipAddresses of inet[] type). I need to query that a single ip is contained in the array of ipAddresses field which can be array of ip addresses or ip address ranges or both. I have tried to add where query like this:

WHERE "ipAddresses" @> '192.168.1.5'::inet

and also like this

WHERE "ipAddresses" >> '192.168.1.5'::inet

but no luck. Thanks in advance.

id          uuid      NOT NULL  gen_random_uuid()
permissions uuid[]      
ipAddresses inet[]      
createdAt   timestamp with time zone        now()
updatedAt   timestamp with time zone        now()

source: pg array functions and pg network address functions

András Váczi · Accepted Answer · 2017-07-21T10:52:42.163

While McNets's solutions would also work, it's possibly the easiest to use ANY with an operator, in this case equality:

SELECT '10.10.10.10' = ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

The background is that an inet array is no different from any other array type in this regard - ANY will work with any boolean-returning operator that is defined on the base type.

The above applies to equality of single IP addresses. If you need to match a single IP against a range, you'll need the <<= operator instead of =:

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

 ?column? 
──────────
 t

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.0/28, 10.10.10.20}'::inet[]);

 ?column? 
──────────
 t

In your case, the solution would look like

WHERE '192.168.1.5' <<= ANY ("ipAddresses")

The performance of this can be poor for the lack of really efficient indexing. To address this, see the suggestion in McNets's answer.

score 2 · Answer 2 · edited Jun 15 '20 at 09:05

Let me know if this answer can helps you, I've set up next sample on dbfiddle.uk.

create table myt 
(
id          uuid      NOT NULL,
ipAddresses inet[],      
createdAt   timestamp with time zone,
updatedAt   timestamp with time zone
);

insert into myt values
('0e37df36-f698-11e6-8dd4-cb9ced3df976'::uuid, array['10.0.0/24','20.0.0.1']::inet[], now(), now()),
('0e37df36-f698-11e6-8dd4-cb9ced3df977'::uuid, array['10.0.0.2','20.0.0.2']::inet[], now(), now()),
('0e37df36-f698-11e6-8dd4-cb9ced3df978'::uuid, array['10.0.0.4','20.0.0.3']::inet[], now(), now()),
('0e37df36-f698-11e6-8dd4-cb9ced3df979'::uuid, array['10.0.0.4']::inet[], now(), now())
;

I'm not really good using arrays, neither on postgres, but as far as you are querying an array, you should use {} to compare elements of an array.

select *
from   myt
where  ipaddresses @> '{10.0.0.4}'

id                                   | ipaddresses         | createdat                     | updatedat                    
:----------------------------------- | :------------------ | :---------------------------- | :----------------------------
0e37df36-f698-11e6-8dd4-cb9ced3df978 | {10.0.0.4,20.0.0.3} | 2017-07-21 11:06:04.547226+01 | 2017-07-21 11:06:04.547226+01
0e37df36-f698-11e6-8dd4-cb9ced3df979 | {10.0.0.4}          | 2017-07-21 11:06:04.547226+01 | 2017-07-21 11:06:04.547226+01

If you are looking if an ip range contains an specific IP, I think you should unnest array elements and then use >> operator.

select * 
from (select id, unnest(ipaddresses) ip
      from   myt) t1
where t1.ip::inet >> '10.0.0.4'::inet

id                                   | ip         
:----------------------------------- | :----------
0e37df36-f698-11e6-8dd4-cb9ced3df976 | 10.0.0.0/24

Additionaly let me suggest you to have a look at ip4r module.

dbfiddle here

Postgres inet array contains a single ip

2 Answers2