Databases - Practical PostgreSQL
Previous Page	Home	Next Page

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 on 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: 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 the reference entry on CREATE TYPE for more on this command.

Table 3-10 lists the data types officially supported by PostgreSQL, as well as any PostgreSQL recognized aliases (alternative names that 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 that 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 that are non-standard (such as the geometric and spacial types). Therefore, you will not always be able to find equivalent types on other SQL-capable database management systems.

Table 3-10. 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`	A variable length character string, of unlimited length.	PostgreSQL-specific
Numeric types	`smallint`, `int2`	A signed 2-byte integer.	SQL89
	`integer`, `int`, `int4`	A signed, fixed-precision 4-byte number.	SQL92
	`bigint`, `int8`	A signed 8-byte integer, up to 18 digits in length.	PostgreSQL-specific
	`real`, `float4`	A 4-byte 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, U.S.-style currency.	PostgreSQL-specific, deprecated.
	`serial`	An auto-incrementing 4-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 (includes time zone)`	Both the date and time.	SQL92
	`interval`	An arbitrarily specified length of time.	SQL92
Geometric types	`box`	A rectangular box in a 2D plane.	PostgreSQL-specific
	`line`	An infinite line in a 2D plane.	PostgreSQL-specific
	`lseg`	A 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`	A 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
System types	`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 there are a couple of topics worth discussing, including the NULL keyword.

Databases - Practical PostgreSQL
Previous Page	Home	Next Page