Skip to content
Coordinator DB
View as Markdown
View as Markdown

IP address and network functions ​

Network functions inspect addresses and subnet prefixes. inet can retain host bits with a prefix; cidr represents a network and validates its host bits. IPv4 and IPv6 native values can be used where the function admits an address.

Functions parse numeric addresses, not hostnames, and do not perform DNS lookups. Prefix lengths range from 0 through 32 for IPv4 and 0 through 128 for IPv6.

Example: Separating a host from its network ​

sql
SELECT CAST(network('192.0.2.17/24') AS TEXT),
       host('192.0.2.17/24'), masklen('192.0.2.17/24')
FORMAT csv;
text
"192.0.2.0/24","192.0.2.17",24

broadcast ​

Set every host bit in an address.

Syntax ​

text
broadcast(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

inet

Example ​

sql
SELECT CAST(broadcast('192.168.1.5/24') AS TEXT) FORMAT csv;
text
"192.168.1.255/24"

family ​

Return 4 for IPv4 or 6 for IPv6.

Syntax ​

text
family(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

INTEGER

Example ​

sql
SELECT family('::1') FORMAT csv;
text
6

host ​

Return address text without its prefix.

Syntax ​

text
host(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

String

Example ​

sql
SELECT host('192.168.1.5/24') FORMAT csv;
text
"192.168.1.5"

hostmask ​

Return the inverse network mask as an address.

Syntax ​

text
hostmask(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

inet

Example ​

sql
SELECT CAST(hostmask('192.168.1.5/24') AS TEXT) FORMAT csv;
text
"0.0.0.255"

inet_merge ​

Find the smallest subnet covering both inputs.

Syntax ​

text
inet_merge(address,other)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

cidr

Example ​

sql
SELECT CAST(inet_merge('192.168.1.5/24','192.168.2.5/24') AS TEXT) FORMAT csv;
text
"192.168.0.0/22"

inet_same_family ​

Test whether both addresses have the same family.

Syntax ​

text
inet_same_family(address,other)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

Bool

Example ​

sql
SELECT inet_same_family('192.168.1.5','::1') FORMAT csv;
text
false

masklen ​

Return the prefix length in bits.

Syntax ​

text
masklen(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

INTEGER

Example ​

sql
SELECT masklen('192.168.1.5/24') FORMAT csv;
text
24

netmask ​

Return the network mask as an address.

Syntax ​

text
netmask(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

inet

Example ​

sql
SELECT CAST(netmask('192.168.1.5/24') AS TEXT) FORMAT csv;
text
"255.255.255.0"

network ​

Return the subnet with cleared host bits.

Syntax ​

text
network(address)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

cidr

Example ​

sql
SELECT CAST(network('192.168.1.5/24') AS TEXT) FORMAT csv;
text
"192.168.1.0/24"

set_masklen ​

Change a prefix, clearing host bits for cidr.

Syntax ​

text
set_masklen(address,prefix)

Arguments ​

Native IPv4/IPv6/inet/cidr or bounded full IPv4/IPv6 text with optional /prefix. Preserve inet host bits, validate cidr host bits. Prefixes 0..32 or 0..128. No DNS. NULL propagates.

Returned value ​

inet or cidr

Example ​

sql
SELECT CAST(set_masklen('192.168.1.5/24',16) AS TEXT) FORMAT csv;
text
"192.168.1.5/16"