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.

Note 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.