POSTGRESQL supports a large number of
data types, as shown in Table ![[*]](cross_ref_motif.gif)
.
Character string types are the most commonly used data types. They can hold any sequence of letters, digits, punctuation, and other valid characters. Typical character strings are names, descriptions, and mailing addresses. You can store any value in a character string. Nevertheless, this type should be used only when other data types are inappropriate, as other types provide better data validation, more compact storage, and better performance.
Three character string data types exist: TEXT, VARCHAR(length), and CHAR(length). TEXT does not limit the number of characters stored. VARCHAR(length) limits the length of the field to length characters. Both TEXT and VARCHAR() store only the number of characters in the string. CHAR(length) is similar to VARCHAR(), except it always stores exactly length characters. This type pads the value with trailing spaces to achieve the specified length, and provides slightly faster access than TEXT or VARCHAR().
Understanding why character string types differ from other data types can be difficult. For example, you can store 763 as a character string. In that case, you will store the symbols 7, 6, and 3, not the numeric value 763. Consequently, you cannot add a number to the character string 763, because it does not make sense to add a number to three symbols. Similarly, the character string 3/8/1992 consists of eight symbols starting with 3 and ending with 2. If you store this value in a character string data type, it is not a date. You cannot sort the string with other values and expect them to be in chronological order. The string 1/4/1998 is less than 3/8/1992 when both are sorted as character strings because 1 is less than 3.
These examples illustrate why the other data types are valuable. The other types use predefined formats for their data, and they support more appropriate operations on the stored information.
Nevertheless, there is nothing wrong with storing numbers or dates in character strings when appropriate. The street address 100 Maple Avenue is best stored in a character string type, even though a number is part of the street address. It makes no sense to store the street number in a separate INTEGER field. Also, part numbers such as G8223-9 must be stored in character strings because of the G and dash. In fact, part numbers that are always five digits, such as 32911 or 00413, should be stored in character strings as well. They are not real numbers, but symbols. Leading zeros cannot be displayed by INTEGER fields, but are easily displayed in character strings.
Number types allow the storage of numbers. The number types are INTEGER , INT2 , INT8 , OID , NUMERIC() , FLOAT , and FLOAT4 .
INTEGER, INT2, and INT8 store whole numbers of various ranges. Larger ranges require more storage. For example, INT8 requires twice the storage of INTEGER and is slower that INTEGER .
OID is used to store POSTGRESQL object identifiers. Although you could use INTEGER for this purpose, OID better documents the meaning of the value stored in the column.
NUMERIC(precision, decimal) allows user-defined digits of precision, rounded to decimal places. This type is slower than the other number types.
FLOAT and FLOAT4 allow storage of floating-point values. Numbers are stored using 15 (FLOAT) or 6 (FLOAT4) digits of precision. The location of the decimal point is stored separately, so large values such as 4.78145e+32 can be represented. FLOAT and FLOAT4 are fast and have compact storage, but can produce imprecise rounding during computations. When you require complete accuracy of floating-point values, use NUMERIC() instead. For example, store monetary amounts as NUMERIC().
Temporal types allow storage of date, time, and time interval information. Although these data can be stored in character strings, it is better to use temporal types, for the reasons outlined earlier in this chapter.
The four temporal types are DATE , TIME ,
TIMESTAMP , and INTERVAL .
DATE allows storage of a single date consisting of a year, month,
and day. The format used to input and display dates is controlled
by the DATESTYLE setting (see Section
on page ). TIME allows storage of
an hour, minute, and second, separated by colons. TIMESTAMP
stores both the date and the time--for example, 2000-7-12 17:34:29.
INTERVAL represents an interval of time, like 5 hours or
7 days. INTERVAL values are often generated by subtracting
two TIMESTAMP values to find the elapsed time. For example,
1996-12-15 19:00:40 minus 1996-12-8 14:00:10 results
in an INTERVAL value of 7 05:00:30, which is 7 days,
5 hours, and 30 seconds. Temporal types can also handle time
zone designations.
The only logical type is BOOLEAN . A BOOLEAN field can store only true or false, and of course NULL. You can input true as true, t, yes, y, or 1. False can be input as false, f, no, n, or 0. Although true and false can be input in a variety of ways, true is always output as t and false as f.
The geometric types support storage of geometric primitives. They
include POINT, LSEG, PATH, BOX, CIRCLE,
and POLYGON. Table shows the geometric
types and typical values for each.
|
The network types are INET , CIDR , and MACADDR . INET allows storage of an IP address, with or without a netmask. A typical INET value with a netmask is 172.20.90.150 255.255.255.0. CIDR stores IP network addresses. It allows a subnet mask to specify the size of the network segment. A typical CIDR value is 172.20.90.150/24. MACADDR stores MAC (Media Access Control) addresses, which are assigned to Ethernet network cards at the time of their manufacture. A typical MACADDR value is 0:50:4:1d:f6:db.
A variety of types are used internally. Psql's \dT command shows all data types.
2 billion range, internally INT4
32 thousand range
range