Network Address Data Types

   

PostgreSQL supports three data types that are designed to hold network addresses, both IP [8] (logical) and MAC [9] (physical) addresses. I don't think there are many applications that require the storage of an IP or MAC address, so I won't spend too much time describing them. The PostgreSQL User's Guide contains all the details that you might need to know regarding network data types.

[8] IP stands for Internet Protocol, the substrate of the Internet.

[9] The acronym MAC stands for one or more of the following: Machine Address Code, Media Access Control, or Macaroni And Cheese.

MACADDR

The MACADDR type is designed to hold a MAC address. A MAC address is a hardware address, usually the address of an ethernet interface.

CIDR

The CIDR data type is designed to hold an IP network address. A CIDR value contains an IP network address and an optional netmask (the netmask determines the number of meaningful bits in the network address).

INET

An INET value can hold the IP address of a network or of a network host. An INET value contains a network address and an optional netmask. If the netmask is omitted, it is assumed that the address identifies a single host (in other words, there is no discernible network component in the address).

Note that an INET value can represent a network or a host, but a CIDR is designed to represent the address of a network.

Syntax for Literal Values

The syntax required for literal network values is shown in Table 2.27.

Table 2.27. Literal Syntax for Network Types

Type

Syntax

Examples

INET

a.b.c.d[/e]

192.168.0.1

192.168.150.0/26

130.155.16.1/20

CIDR

a[.b[.c[.d]]][/e]

192.168.0.0/16

192.168/16

MACADDR

xxxxxx:xxxxxx

xxxxxx-xxxxxx

xxxx.xxxx.xxxx

xx-xx-xx-xx-xx-xx

xx:xx:xx:xx:xx:xx

0004E2:3695C0

0004E2-3695C0

0004.E236.95C0

00-04-E2-36-95-C0

00:04:E2:36:95:C0

An INET or CIDR value consumes 12 bytes of storage. A MACADDR value consumes 6 bytes of storage.

Supported Operators

PostgreSQL provides comparison operators that you can use to compare two INET values, two CIDR values, or two MACADDR values. The comparison operators work by first checking the common bits in the network components of the two addresses; then, if those are equal, the address with the greatest number of netmask bits is considered the largest value. If the number of bits in the netmask is equal (and the network components of the addresses are equal), then the entire address is compared. The net effect (pun intended) is that 192.168.0.22/24 is considered greater than 192.168.0.22/20 .

When you are working with two INET (or CIDR) values, you can also check for containership. Table 2.28 describes the network address operators.

Table 2.28. Network Address Operators

Operator

Meaning

INET 1 < INET 2

CIDR 1 < CIDR 2

MACADDR 1 < MACADDR 2

True if operand 1 is less than operand 2

INET 1 <= INET 2

CIDR 1 <= CIDR 2

MACADDR 1 <= MACADDR 2

True if operand 1 is less than or equal to operand 2

INET 1 <> INET 2

CIDR 1 <> CIDR 2

MACADDR 1 <> MACADDR 2

True if operand 1 is not equal to operand 2

INET 1 = INET 2

CIDR 1 = CIDR 2

MACADDR 1 = MACADDR 2

True if operand 1 is equal to operand 2

INET 1 >= INET 2

CIDR 1 >= CIDR 2

MACADDR 1 >= MACADDR 2

True if operand 1 is greater than or equal to operand 2

INET 1 > INET 2

CIDR 1 > CIDR 2

MACADDR 1 > MACADDR 2

True if operand 1 is greater than operand 2

INET 1 << INET 2

CIDR 1 << CIDR 2

TRUE if operand 1 is contained within operand 2

INET 1 <<= INET 2

CIDR 1 <<= CIDR 2

True if operand 1 is contained within operand 2 or if operand 1 is equal to operand 2

INET 1 >> INET 2

CIDR 1 >> CIDR 2

True if operand 1 contains operand 2

INET 1 >>= INET 2

CIDR 1 >>= CIDR 2

True if operand 1 contains operand 2 or if operand 1 is equal to operand 2

   


PostgreSQL
PostgreSQL (2nd Edition)
ISBN: 0672327562
EAN: 2147483647
Year: 2005
Pages: 220
Authors: Korry Douglas

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net