Network

PostgreSQL Data Type Network
Categories:
Tags:

Network Fields

Type Size Description
cidr 7 or 19 bytes IPv4 and IPv6 networks
inet 7 or 19 bytes IPv4 and IPv6 hosts and networks
macaddr 6 bytes MAC addresses
macaddr8 8 bytes MAC addresses (EUI-64 format)

Select Example

ip_geo_test example table

network name created_at
1.1.0.0/20 cidr 20 2022-12-22 17:56:28.973 +0800
1.1.1.0/24 cidr 24 2022-12-22 17:56:28.977 +0800
1.1.1.0/27 cidr 27 2022-12-22 17:59:28.753 +0800
1.1.0.0/19 cidr 19 2022-12-22 17:59:57.920 +0800
1.1.1.2/32 cidr 32 Specific IP 1.1.1.2 2022-12-22 17:57:26.194 +0800
1.1.1.1/32 cidr 32 Specific IP 1.1.1.2 2022-12-22 17:57:26.188 +0800 
CREATE TABLE public.ip_geo_test (
  network cidr NOT NULL,
  "name" varchar(30) NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT ip_geo_test_pk PRIMARY KEY (network)
);
CREATE INDEX ipgtest ON public.ip_geo_test USING gist (network inet_ops);

Get the parent subnet include self ip address

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1/32'

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1'

-- | 1.1.0.0/20 | cidr 20 | 2022-12-22 17:56:28.973 +0800 |
-- | 1.1.1.0/24 | cidr 24 | 2022-12-22 17:56:28.977 +0800 |
-- | 1.1.1.0/27 | cidr 27 | 2022-12-22 17:59:28.753 +0800 |
-- | 1.1.0.0/19 | cidr 19 | 2022-12-22 17:59:57.920 +0800 |
-- | 1.1.1.1/32 | cidr 32 Specific IP 1.1.1.2 | 2022-12-22 17:57:26.188 +0800 |

Get the parent subnet

select * from ip_geo_test igt  
where network >> inet '1.1.1.1/32'

select * from ip_geo_test igt  
where network >> inet '1.1.1.1'

-- | 1.1.0.0/20 | cidr 20 | 2022-12-22 17:56:28.973 +0800 |
-- | 1.1.1.0/24 | cidr 24 | 2022-12-22 17:56:28.977 +0800 |
-- | 1.1.1.0/27 | cidr 27 | 2022-12-22 17:59:28.753 +0800 |
-- | 1.1.0.0/19 | cidr 19 | 2022-12-22 17:59:57.920 +0800 |

Get the parent subnet include self ip address and order by cidr

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1/32'
order by network desc

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1'
order by network desc


-- | 1.1.1.1/32 | cidr 32 Specific IP 1.1.1.2 | 2022-12-22 17:57:26.188 +0800 |
-- | 1.1.1.0/27 | cidr 27 | 2022-12-22 17:59:28.753 +0800 |
-- | 1.1.1.0/24 | cidr 24 | 2022-12-22 17:56:28.977 +0800 |
-- | 1.1.0.0/20 | cidr 20 | 2022-12-22 17:56:28.973 +0800 |
-- | 1.1.0.0/19 | cidr 19 | 2022-12-22 17:59:57.920 +0800 |

Get the specific ip or the nearest parent subnet

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1/32'
order by network desc
limit 1

select * from ip_geo_test igt  
where network >>= inet '1.1.1.1'
order by network desc
limit 1

-- | 1.1.1.1/32 | cidr 32 Specific IP 1.1.1.2 | 2022-12-22 17:57:26.188 +0800 |


select * from ip_geo_test igt  
where network >>= inet '1.1.1.3/32'
order by network desc
limit 1

select * from ip_geo_test igt  
where network >>= inet '1.1.1.3'
order by network desc
limit 1

-- | 1.1.1.0/27 | cidr 27 | 2022-12-22 17:59:28.753 +0800 |

Create Index

CIDR Index

-- Syntax
CREATE INDEX {index_name} ON {cidr_table} USING gist ({cidr_field_name} inet_ops)

-- Example
CREATE INDEX my_cidr_index ON ip_geo USING gist (network_cidr inet_ops)

IP Address Operators

Operator Description Example
<< is contained by inet '192.168.1.5' << inet '192.168.1/24'
<<= is contained by or equals inet '192.168.1/24' <<= inet '192.168.1/24'
>> contains inet '192.168.1/24' >> inet '192.168.1.5'
>>= contains or equals inet '192.168.1/24' >>= inet '192.168.1/24'
&& contains or is contained by inet '192.168.1/24' && inet '192.168.1.80/28'
~ bitwise NOT ~ inet '192.168.1.6'
& bitwise AND inet '192.168.1.6' & inet '0.0.0.255'
| bitwise OR inet '192.168.1.6' | inet '0.0.0.255'
+ addition inet '192.168.1.6' + 25
- subtraction inet '192.168.1.43' - 36 or inet '192.168.1.43' - inet '192.16
= equals inet '192.168.1.5' = inet '192.168.1.5'
<> is not equal inet '192.168.1.5' <> inet '192.168.1.4'
< is less than inet '192.168.1.5' < inet '192.168.1.6'
<= is less than or equal inet '192.168.1.5' <= inet '192.168.1.5'
>= is greater or equal inet '192.168.1.5' >= inet '192.168.1.5'
> is greater than inet '192.168.1.5' > inet '192.168.1.4'

is contained by

inet << inet → boolean

Is subnet strictly contained by subnet?

SQL Result
SELECT inet '192.168.1.5' << inet '192.168.1/24' true
SELECT inet '192.168.0.5' << inet '192.168.1/24' false
SELECT inet '192.168.1/24' << inet '192.168.1/25' false
SELECT inet '192.168.1/24' << inet '192.168.1/24' false
SELECT inet '192.168.1/24' << inet '192.168.1/23' true

is contained by or equals

inet <<= inet → boolean

Is subnet contained by or equal to subnet?

SQL Result
SELECT inet '192.168.1/24' <<= inet '192.168.1/25' false
SELECT inet '192.168.1/24' <== inet '192.168.1/24' true
select inet '192.168.1/24' <<= inet '192.168.1/23' true

contains

inet >> inet → boolean

Does subnet strictly contain subnet?

SQL Result
SELECT inet '192.168.1/24' >> inet '192.168.1.5' true
SELECT inet '192.168.1/24' >> inet '192.168.1.6' true
SELECT inet '192.168.1/24' >> inet '192.168.2.6' false

contains or equals

inet >>= inet → boolean

Does subnet contain or equal subnet?

SQL Result
SELECT inet '192.168.1/24' >>= inet '192.168.1/25' true
SELECT inet '192.168.1/24' >>= inet '192.168.1/24' true
SELECT inet '192.168.1/24' >>= inet '192.168.1/23' false

contains or is contained by

inet && inet → boolean

Does either subnet contain or equal the other?

SQL Result
SELECT inet '192.168.1/24' && inet '192.168.1.80/28' true
SELECT inet '192.168.1/24' && inet '192.168.2.80/28' false
SELECT inet '192.168.1/24' && inet '192.168/16' true
SELECT inet '192.168.1/24' && inet '192.168.1/24' true
SELECT inet '192.168.1/24' && inet '192.168.1.1/32' true
SELECT inet '192.168.1/24' && inet '192.168.2/24' false

bitwise NOT

~ inet → inet

Computes bitwise NOT

SQL Result
select ~ inet '192.168.1.1' 63.87.254.254
select ~ inet '192.168.1.16' 63.87.254.239
select ~ inet '192.168.1.32' 63.87.254.223
select ~ inet '192.168.1.64' 63.87.254.191
select ~ inet '192.168.1.128' 63.87.254.127
select ~ inet '192.168.1.255' 63.87.254.0

bitwise AND

inet & inet → inet

Computes bitwise AND

SQL Result
SELECT inet '192.168.1.6' & inet '0.0.0.255' 0.0.0.6
SELECT inet '192.168.1.6' & inet '0.0.255.255' 0.0.1.6
SELECT inet '192.168.1.6' & inet '0.255.255.255' 0.168.1.6
SELECT inet '192.168.1.6' & inet '255.255.255.255' 192.168.1.6

bitwise OR

inet | inet → inet

Computes bitwise OR

SQL Result
SELECT inet '192.168.1.6' | inet '0.0.0.255' 192.168.1.255
SELECT inet '192.168.1.6' | inet '0.0.255.255' 192.168.255.255
SELECT inet '192.168.1.6' | inet '0.255.255.255' 192.255.255.255
SELECT inet '192.168.1.6' | inet '255.255.255.255' 255.255.255.255

addition

inet + bigint → inet

bigint + inet → inet

Adds an offset to an address.

SQL Result
SELECT inet '192.168.0.0' + 16 192.168.0.16
SELECT inet '192.168.0.0' + 32 192.168.0.32
SELECT inet '192.168.0.0' + 64 192.168.0.64
SELECT inet '192.168.0.0' + 128 192.168.0.128
SELECT inet '192.168.0.0' + 255 192.168.0.255
SELECT inet '192.168.0.0' + 256 192.168.1.0
SELECT inet '192.168.0.0' + 512 192.168.2.0
SELECT inet '192.168.0.0' + 1024 192.168.4.0
SELECT 16 + inet '192.168.0.0' 192.168.0.16
SELECT 32 + inet '192.168.0.0' 192.168.0.32
SELECT 200 + inet '::ffff:fff0:1' ::ffff:255.240.0.201

subtraction

inet - bigint → inet

inet - inet → bigint

SQL Result
SELECT inet '192.168.1.0' - 16 192.168.0.240
SELECT inet '192.168.1.0' - 32 192.168.0.224
SELECT inet '192.168.1.0' - 64 192.168.0.192
SELECT inet '192.168.1.0' - 128 192.168.0.128
SELECT inet '192.168.1.0' - 256 192.168.0.0
SELECT inet '192.168.1.0' - inet '192.168.0.240' 16
SELECT inet '192.168.1.0' - inet '192.168.0.224' 32
SELECT inet '192.168.1.0' - inet '192.168.0.192' 64
SELECT inet '192.168.1.0' - inet '192.168.0.128' 128
SELECT inet '192.168.1.0' - inet '192.168.0.0' 256

equals

inet = inet → boolean

SQL Result
SELECT inet '192.168.64.0' = inet '192.168.64/24' false
SELECT inet '192.168.64/24' = inet '192.168.64/24' true

is not equal

inet <> inet → boolean

SQL Result
SELECT inet '192.168.64.0' <> inet '192.168.64/24' true
SELECT inet '192.168.64/24' <> inet '192.168.64/24' false

is less than

inet < inet → boolean

SQL Result
SELECT inet '192.168.1.1' < inet '192.168.64/24' true
SELECT inet '192.168.16.1' < inet '192.168.64/24' true
SELECT inet '192.168.32.1' < inet '192.168.64/24' true
SELECT inet '192.168.64.1' < inet '192.168.64/24' false
SELECT inet '192.168.128.1' < inet '192.168.64/24' false
SELECT inet '192.168.255.1' < inet '192.168.64/24' false
SELECT inet '192.168.64/24' < inet '192.168.64/24' false

is less than or equal

inet <= inet → boolean

SQL Result
SELECT inet '192.168.1.0' <= inet '192.168.64/24' true
SELECT inet '192.168.16.0' <= inet '192.168.64/24' true
SELECT inet '192.168.32.0' <= inet '192.168.64/24' true
SELECT inet '192.168.64.0' <= inet '192.168.64/24' false
SELECT inet '192.168.128.0' <= inet '192.168.64/24' false
SELECT inet '192.168.255.0' <= inet '192.168.64/24' false
SELECT inet '192.168.64/24' <= inet '192.168.64/24' true

is greater or equal

inet >= inet → boolean

SQL Result
SELECT inet '192.168.1.0' >= inet '192.168.64/24' false
SELECT inet '192.168.16.0' >= inet '192.168.64/24' false
SELECT inet '192.168.32.0' >= inet '192.168.64/24' false
SELECT inet '192.168.64.0' >= inet '192.168.64/24' true
SELECT inet '192.168.128.0' >= inet '192.168.64/24' true
SELECT inet '192.168.255.0' >= inet '192.168.64/24' true
SELECT inet '192.168.64/24' >= inet '192.168.64/24' true

is greater than

inet > inet → boolean

SQL Result
SELECT inet '192.168.1.0' > inet '192.168.64/24' false
SELECT inet '192.168.16.0' > inet '192.168.64/24' false
SELECT inet '192.168.32.0' > inet '192.168.64/24' false
SELECT inet '192.168.64.0' > inet '192.168.64/24' true
SELECT inet '192.168.128.0' > inet '192.168.64/24' true
SELECT inet '192.168.255.0' > inet '192.168.64/24' true
SELECT inet '192.168.64/24' > inet '192.168.64/24' false

IP Address Functions

Function Return Type Description Example Result
abbrev(inet) text abbreviated display format as text abbrev(inet '10.1.0.0/16') 10.1.0.0/16
abbrev(cidr) text abbreviated display format as text abbrev(cidr '10.1.0.0/16') 10.1/16
broadcast(inet) inet broadcast address for network broadcast('192.168.1.5/24') 192.168.1.255/24
family(inet) int extract family of address;4for IPv4,6for IPv6 family('::1') 6
host(inet) text extract IP address as text host('192.168.1.5/24') 192.168.1.5
hostmask(inet) inet construct host mask for network hostmask('192.168.23.20/30') 0.0.0.3
masklen(inet) int extract netmask length masklen('192.168.1.5/24') 24
netmask(inet) inet construct netmask for network netmask('192.168.1.5/24') 255.255.255.0
network(inet) cidr extract network part of address network('192.168.1.5/24') 192.168.1.0/24
set_masklen(inet,int) inet set netmask length forinetvalue set_masklen('192.168.1.5/24', 16) 192.168.1.5/16
set_masklen(cidr,int) cidr set netmask length forcidrvalue set_masklen('192.168.1.0/24'::cidr, 16) 192.168.0.0/16
text(inet) text extract IP address and netmask length as text text(inet '192.168.1.5') 192.168.1.5/32
inet_same_family(inet,inet) boolean are the addresses from the same family? inet_same_family('192.168.1.5/24', '::1') FALSE
inet_merge(inet,inet) cidr the smallest network which includes both of the given networks inet_merge('192.168.1.5/24', '192.168.2.5/24') 192.168.0.0/22

MAC Address Functions

Function Return Type Description Example Result
trunc(macaddr) macaddr set last 3 bytes to zero trunc(macaddr '12:34:56:78:90:ab') 12:34:56:00:00:00
trunc(macaddr8) macaddr8 set last 5 bytes to zero trunc(macaddr8 '12:34:56:78:90:ab:cd:ef') 12:34:56:00:00:00:00:00
macaddr8_set7bit(macaddr8) macaddr8 set 7th bit to one, also known as modified EUI-64, for inclusion in an IPv6 address macaddr8_set7bit(macaddr8 '00:34:56:ab:cd:ef') 02:34:56:ff:fe:ab:cd:ef

Reference