Network Address Types in PostgreSQL. Why you need to know?

Difference between data types and network address types in PostgrSQL.

Along with data types PostgreSQL offers some other data types called network address types. So, the difference between other data types and network address types is that network address types are used to store IPV4, IPV6 and MAC addresses. while other data types are simple data types like any other programming language supports.

There are four network address types.

• inet
• cidr
• macaddr
• macaddr8

inet

This network address type holds IPV4 and IPV6 host address, and optionally its subnet, all in one field. It takes 7 or 19 bytes of storage. Subnet is basically represented by the total number of network address bits present in the host. For example, if the subnet is 32 and the address is IPV4, then it represents a single host, not the subnet. And similarly for IPV6, the address length is 128 bits. So, 128 bits uniquely represent a host address.
Input format is address/y. Where address is an IPV4 or IPV6 address and y is the number of bits in the subnet. If /y is not given then it represents a single host.

cidr

This network address type only holds IPV4 or IPV6 networks. Main thing about this is input and output format follow classless internet domain routing. Its format is almost same that of inet. If /y is not given then it represents a single host.

So, whats the difference?

The main difference between inet and cidr is that unlike cidr, inet accepts nonzero bits to the right of the netmask.

For example network address.

192.168.0.1/24

is valid for inet but not for cidr.

macaddr

As the name suggests, macaddr stores the MAC addresses. For example Ethernet card hardware addresses.
So, it accepts inputs of the following formats.

'08:00:2b:01:02:03'
'08-00-2b-01-02-03'
'08002b:010203'
'08002b-010203'
'0800.2b01.0203'
'0800-2b01-0203'
'08002b010203'

These examples all specify the same address. It accepts both capital and lower case letter for a to f.

macaddr8

This type of network address types also stores MAC addresses but in EUI-64 format. Main thing about this is, it can accept both 6 and 8 bytes length and stores them in fixed length of 8 bytes, with 4th and 5th bytes set to FF and FE.

Following are the examples of this,

'08:00:2b:01:02:03:04:05'
'08-00-2b-01-02-03-04-05'
'08002b:0102030405'
'08002b-0102030405'
'0800.2b01.0203.0405'
'0800-2b01-0203-0405'
'08002b01:02030405'
'08002b0102030405'

Same like macaddr these all examples specify the same address. same like macaddr it can accept both upper and lower case for the digit a to f in the input.

That was a short introduction about network address types that PostgreSQL additionally supports along with other data types. To read in more details you can check reference.

Reference

https://www.postgresql.org/docs/14/datatype-net-types.html