TIL - Postgres MAC Address Data Type

macaddr

I was adding a new column to a table in a PostgreSQL database to store IP addresses but couldn't remember the correct data type—whether it was ipaddr, ipnet, inet, or something else. So, I referred to the documentation and discovered not only that the correct data type is inet but also that PostgreSQL offers a data type for MAC addresses, called macaddr.

CREATE TABLE devices (
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	mac_address MACADDR
);

INSERT INTO devices (name, mac_address) VALUES ('Router', '08:00:2b:01:02:03');

test-db=# select * from devices;
 id |  name  |    mac_address    
----+--------+-------------------
  1 | Router | 08:00:2b:01:02:03
(1 row)

test-db=# \d devices
                                      Table "public.devices"
   Column    |          Type          | Collation | Nullable |               Default               
-------------+------------------------+-----------+----------+-------------------------------------
 id          | integer                |           | not null | nextval('devices_id_seq'::regclass)
 name        | character varying(50)  |           |          | 
 mac_address | macaddr                |           |          | 
Indexes:
    "devices_pkey" PRIMARY KEY, btree (id)

• The macaddr data type is 6 bytes (48 bits) in size.

macaddr8

• The macaddr8 data type, which is 8 bytes (64 bits) in size, accommodates modern standards requiring more than the traditional 48 bits. Therefore, macaddr8 can handle both 48-bit and 64-bit MAC addresses, which makes it more versatile and future-proof.

CREATE TABLE devices_macaddr8 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    mac_address MACADDR8
);

INSERT INTO devices_macaddr8 (name, mac_address) VALUES ('Router', '08:00:2b:01:02:03:04:05');

test-db=# select * from devices_macaddr8;
 id |  name  |       mac_address       
----+--------+-------------------------
  1 | Router | 08:00:2b:01:02:03:04:05
(1 row)

test-db=# \d devices_macaddr8
                                      Table "public.devices_macaddr8"
   Column    |          Type          | Collation | Nullable |                   Default                    
-------------+------------------------+-----------+----------+----------------------------------------------
 id          | integer                |           | not null | nextval('devices_macaddr8_id_seq'::regclass)
 name        | character varying(50)  |           |          | 
 mac_address | macaddr8               |           |          | 
Indexes:
    "devices_macaddr8_pkey" PRIMARY KEY, btree (id)

Converting a table from macaddr to macaddr8 using the macaddr8_set7bit function

ALTER TABLE your_table ADD COLUMN new_mac_column macaddr8; -- add new column
UPDATE your_table SET new_mac_column = macaddr8_set7bit(old_mac_column); -- convert old mac to new mac
ALTER TABLE your_table DROP COLUMN old_mac_column; -- drop old column
ALTER TABLE your_table RENAME COLUMN new_mac_column TO old_mac_column; -- rename new column to old column

• Obviously be cautious and keep backups

Takeaway

• If you're storing mac addresses in a postgres database, there's a data type for that called macaddr. And for an additional 2 bytes per record, you can ditch macaddr and use macaddr8 to future proof your database...until 8 bytes are no longer enough 😅️.

🐘️