Network Data Types | PostgreSQL Essential Reference

I l @ ve RuBoard

PostgreSQL is unique among many SQL systems in that it includes built-in data types for network addresses. CIDR , INET , and MACADDR all represent specific aspects of network addresses. These data types can be particularly useful when using PostgreSQL as a back-end database to a web application.

Storing network values in these data types is preferential due to the included functions in PostgreSQL that act on network-specific data types.

`CIDR`

Description

Holds dotted -quad data for an IP address and the number of bits in the netmask . This data type is named for the Classless Internet Domain Routing (CIDR) convention.

Inputs

 x.x.x.x/y

x.x.x. x ”Valid IP address.

y ”Bits in the netmask.

Storage Size

12 bytes

Example Data

192.168.0.1/24

128.1 (128.1.0.0/16 assumed)

10 (10.0.0.0/8 assumed)

Notes

If the bits from the netmask are omitted, the netmask bits are assumed by using the class of the dotted-quad (for example, 255.0.0.0 assumes 8, 255.255.0.0 assumes 16, 255.255.255.0 assumes 24, and so on). However, the assumption will be large enough to handle all the entries in the expressed octets.

IPv6 is not yet supported.

`INET`

Description

Holds dotted-quad data for an IP address and an optional netmask.

Inputs

 x.x.x.x/y

x.x.x. x ”Valid IP address.

y ”If given, a netmask; otherwise , a host is assumed.

Storage Size

12 bytes

Example Data

192.168.0.1 (192.168.0.1/32 is assumed)

Notes

The difference between this and CIDR is that an INET can refer to a single host, whereas CIDR refers to an IP network.

`MACADDR`

Description

Holds a MAC address, which is an ethernet hardware address.

Inputs

Several different formats are supported, such as the following

 xxxxxx:xxxxxx  xxxxxx-xxxxxx  xxxx.xxxx.xxxx  xx-xx-xx-xx-xx-xx

xx:xx:xx:xx:xx: xx (the default)

Storage Size

6 bytes

Example Data

Both of these refer to the same MAC address:

08-00-2d-01-32-22

08002d:013222

Notes

The directory $SOURCE/ contrib /mac includes tools to identify the manufacturers of specific network cards from a given MAC address. (This directory is specific to Version 7.1 only.)

I l @ ve RuBoard