PostgreSQL에서 inet이나 macaddr을 쓰는 것이 좋을까?

PostgreSQL의 데이터 타입을 보면 네트워크 정보를 저장하기 위해 고안된 타입이 있습니다.

IPv4나 IPv6를 위한 inet과 MAC 주소를 위한 macaddr이 그것이다. cidr도 있지만 개인적으로 사용할 일이 적어 패스합니다.

보통은 varchar를 통해 문자열로 저장을 하는데 이런 타입이 있는 이유는 무엇이고 이런 타입을 쓸 때의 장점에 대해 알아보자.

우선 테스트로 테이블을 만들어보고

CREATE TABLE network

(

  id integer,

  ip_varchar character varying(50),

  mac_varchar character varying(50),

  ip_inet inet,

  mac_macaddr macaddr,

  CONSTRAINT network_pk PRIMARY KEY (id)

);

데이터를 import한다. (포맷: CSV로 / 인코딩: UTF-8) :  network_data.csv

Import 버튼을 누르고 성공하면 Done이 뜬다. 예러가 났다면 설정을 확인하자.

SELECT를 해보면 아래와 같을 것이다.

select * from network;

inet을 쓸 때 장점

가끔 IP를 기준으로 정렬을 할 경우가 있습니다. varchar 타입에 대해 order by를 적용할 경우 뭔가 정렬 순서가 우리가 원하는 경우가 아닐 때가 있습니다.

select id, ip_varchar from network order by ip_varchar;

반면에 inet 타입으로 정렬을 하면 어떻게 될까요?

select id, ip_varchar from network order by ip_inet;

.으로 구분 된 숫자의 크기 대로 정렬이 되었음을 알 수 있습니다.

그 이유는 inet 타입은 텍스트가 아닌 값으로 비교를 했기 때문입니다. 아마도 POSIX의 inet_addr 타입처럼 문자열이 아닌 정수형으로 내부 데이터를 저장했기 때문으로 보입니다.

또 다른 장점으로는 비교 연산자나 해당 타입용 함수들을 쓸 수 있습니다.

이것에 대한 설명은 공식 문서 9.12. Network Address Functions and Operators를 참고하시기 바랍니다.

macaddr을 쓸 때 장점

inet이나 cidr에 비해 macaddr은 사용성이 떨어집니다. 6바이트로 구성되어 있는데, 첫 3바이트는 제조업제의 식별코드OUI(Organizational Unique Identifier)입니다. trunc라는 함수를 macaddr 타입에 적용을 하면 OUI의 바이트만 남기고 나머지 24비트를 00으로 정리를 할 수 있습니다.

select id, mac_varchar, trunc(mac_macaddr) from network;

count함수와 group by를 이용하면 제조사 별로 몇개나 되는지 카운트 해볼 수 있습니다.

select trunc(mac_macaddr) as oui, count(trunc(mac_macaddr)) as count from network

  group by trunc(mac_macaddr);

그렇다면 다른 Database는 어떨까?

MS SQL Server의 경우는 IPv4를 위한 데이터 타입은 없다고 한다.

그래서 다음과 같은 함수를 만들어놓고 Binary(4)에 저장하는 것을 많이 이야기 하고 있다.

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
go

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @str AS VARCHAR(15) 

    SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );

    RETURN @str
END;
go

출처: stackoverflow::Datatype for storing ip address in SQL Server

실행은 아래와 같다.

SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go

Oracle도 별도 데이터 타입은 없는 것 같고 비슷한 접근을 하는 것 같다.

=> stackoverflow::Representing IPv4/IPv6 addresses in Oracle