Network Address Data Types in PostgreSQL - Part 2

In continuation of the last blog, further data types for storing IPv4, IPv6, and MAC addresses in PostgreSQL are discussed. Rather than utilizing basic text types, it is better to use these specific types for storing network addresses.

macaddr

The macaddr type is designed to store MAC addresses also known as Media Access Control addresses, are identifiers assigned to network interfaces.

The macaddr type in PostgreSQL allows you to store MAC addresses in a compact manner. The standard format of six groups of two hexadecimal digits is followed, separated by colons or hyphens. For example, a MAC address may look like "08:00:2b:04:02:05" or "08-00-2b-01-02-03".

The macaddr type provides several benefits. First off, it ensures that the stored values are in the correct format. This helps prevent input errors when working with MAC addresses. It also provides specialized operators and functions for manipulating and comparing MAC addresses, making it easier to perform operations such as sorting and searching based on MAC addresses.

MAC addresses are commonly associated with Ethernet networks. They can also be used for other purposes, such as in Bluetooth devices and Wi-Fi networks.

macaddr8

An extension of the macaddr type in PostgreSQL macaddr8 was introduced in PostgreSQL version 10 to address the limitation of the original macaddr type, which only supported 48-bit MAC addresses.

It is designed to store 64-bit MAC addresses which provides a larger address space to accommodate more unique identifiers. The format is similar to macaddr, with eight groups of two hexadecimal digits. For example, "00:0a:95:9d:28:12:23:52" or "00-0A-45-9D-64-12-23-22".

By using macaddr8, PostgreSQL allows for the storage, manipulation, and comparison of 64-bit MAC addresses. It offers ensuring data integrity and providing specialized operators and functions for working with MAC addresses.

To convert a traditional 48 bit MAC address in EUI-48 format to modified EUI-64 format to be included as the host portion of an IPv6 address, use macaddr8_set7bit as shown: