2

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

4

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.