Data Types
Description
This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Data Types
SQL is considered a strongly typed language . This means that any piece of data represented by PostgreSQL has an associated data type, even if it is not plainly obvious. A data value's type both defines and constrains the kinds of operations which may be performed it.
Not only is every piece of data associated with a type, but types play a large part in the construction of tables. As stated in the section called Introduction to Relational Databases , tables are made up of one or more columns. These columns must, in addition to having a name, have a specific data type.
 | Extending Data Types |
|---|---|
While PostgreSQL provides a wide variety of built-in data types, you also have the option to add new data types to PostgreSQL using the CREATE TYPE command. See Chapter 5 for more information on this extensibility. |
Table 3-9 lists the data types officially supported by PostgreSQL, as well as any PostgreSQL recognized aliases (alternative names which are identical in connotation). There are many other internal (meaning they are no longer intended for normal use) or deprecated (outdated, and discouraged) data types available which are unlisted.
Additionally, while most of the data types implemented in PostgreSQL are directly derived from SQL standards, there are some actively maintained data types which are non-standard (such as the geometric and spacial types). Whether or not you are able to find equivalent types on other SQL-capable database management systems is therefore highly subjective.
Table 3-9. PostgreSQL Supported Data Types
Category | Data type | Description | Standardization |
|---|---|---|---|
Boolean and Binary Types | boolean , bool | A single true or false value. | SQL99 |
bit(n) | An n-length bit string (exactly n binary bits). | SQL92 | |
bit varying(n) , varbit(n) | A variable n -length bit string (up to n binary bits) | SQL92 | |
Character Types | character (n) , char(n) | A fixed n -length character string. | SQL89 |
character varying(n) , varchar(n) | A variable length character string of up to n characters. | SQL92 | |
text | Variable length character string, of unlimited length. | PostgreSQL-specific | |
Numeric Types | smallint , int2 | A signed two-byte integer. | SQL89 |
integer , int , int4 | A signed, fixed-precision four-byte number. | SQL92 | |
bigint , int8 | A signed 8-byte integer, up to 18 digits in length. | PostgreSQL-specific | |
real , float4 | A single precision floating-point number. | SQL89 | |
double precision , float8 , float | An 8-byte floating-point number. | SQL89 | |
numeric(p,s) , decimal(p, s) | An exact numeric type with arbitrary precision p , and scale s . | SQL99 | |
money | A fixed precision, US-style currency. | PostgreSQL-specific, deprecated. | |
serial | An auto-incrementing four-byte integer. | PostgreSQL-specific | |
Date and Time Types | date | The calendar date (day, month and year). | SQL92 |
time | The time of day. | SQL92 | |
time with time zone | The time of day, including time zone information. | SQL92 | |
timestamp [with time zone] | Both the date and time. | SQL92 | |
interval | An arbitrarily specified length of time. | SQL92 | |
Geometric Types | box | A rectangular box in a two-dimensional plane. | PostgreSQL-specific |
line | infinite line in a 2D plane | PostgreSQL-specific | |
lseg | finite line segment in a 2D plane | PostgreSQL-specific | |
circle | a circle with center and radius | PostgreSQL-specific | |
path | Open and closed geometric paths in a two-dimensional plane. | PostgreSQL-specific | |
point | geometric point in a 2D plane | PostgreSQL-specific | |
polygon | closed geometric path in a 2D plane | PostgreSQL-specific | |
Network Types | cidr | An IP network specification | PostgreSQL-specific |
inet | A network IP address, with optional subnet bits | PostgreSQL-specific | |
macaddr | A MAC address (e.g., an Ethernet card's hardware address) | PostgreSQL-specific | |
System Types | oid | An object (row) identifier | PostgreSQL-specific |
xid | A transaction identifier | PostgreSQL-specific |
Remaining true to theme, the following sections on data types will describe in further detail each of the most widely used and practical types. This book will not go into detail on the non-standard and/or more esoteric types, such as the geometric, network and bitwise types.
These sections include information on valid usage, storage considerations, input and output formats and general syntactic conventions. Before we go much further on specific data types, however, a couple of topics worth discussing first are type coercion and the NULL keyword.
Continue to:
My Books
