Chapter 8 Data Types

PostgreSQLThere is a rich set of native data types available. Users can useCREATE TYPEThe command isPostgreSQLAdd new data types.

Table 8.1shows all built-in general-purpose data types. Most of the names listed in“Alias”the column are alternative names that, for historical reasons, arePostgreSQLnames used internally. In addition, there are some internally used or obsolete types available, but they are not listed here.

Table 8.1. Data Types

Name	Alias	Description
`bigint`	`int8`	Signed 8-byte integer
`bigserial`	`serial8`	Auto-incrementing 8-byte integer
`bit [ (n) ]`		Fixed-length Bit Strings
`bit varying [ (n) ]`	`varbit`	Variable-length Bit Strings
`boolean`	`bool`	Logical Boolean value (true/false)
`box`		Ordinary box on a plane
`bytea`		Binary data (“Byte Arrays”）
`character [ (n) ]`	`char [ (n) ]`	Fixed-length Strings
`character varying [ (n) ]`	`varchar [ (n) ]`	Variable-length Strings
`cidr`		IPv4 or IPv6 network address
`circle`		Circles on a Plane
`date`		Calendar date (year, month, day)
`double precision`	`float8`	Double-precision floating-point number (8 bytes)
`inet`		IPv4 or IPv6 host address
`integer`	`int`, `int4`	Signed 4-byte integer
`interval [ fields ] [ (p) ]`		Time period
`json`		Textual JSON data
`jsonb`		Binary JSON data, decomposed
`line`		Infinite line on a plane
`lseg`		Line segment on a plane
`macaddr`		MAC (Media Access Control) Address
`macaddr8`		MAC (Media Access Control) Address (EUI-64 Format)
`money`		Money Supply
`numeric [ (p, s) ]`	`decimal [ (p, s) ]`	Exact number with selectable precision
`path`		Geometric path on a plane
`pg_lsn`		PostgreSQLLog Sequence Number
`point`		Geometric point on a plane
`polygon`		Closed geometric path on a plane
`real`	`float4`	Single-precision floating-point number (4 bytes)
`smallint`	`int2`	Signed 2-byte integer
`smallserial`	`serial2`	Auto-incrementing 2-byte integer
`serial`	`serial4`	Auto-incrementing 4-byte integer
`text`		Variable-length Strings
`time [ (p) ] [ without time zone ]`		Time of day (without time zone)
`time [ (p) ] with time zone`	`timetz`	Time of day, including time zone
`timestamp [ (p) ] [ without time zone ]`		Date and time (without time zone)
`timestamp [ (p) ] with time zone`	`timestamptz`	Date and time, including time zone
`tsquery`		Text search query
`tsvector`		Text search document
`txid_snapshot`		User-level transaction ID snapshot
`uuid`		Universally unique identifier
`xml`		XML Data

Compatibility

The following types (or their spellings) areSQLspecified:bigint、bit、bit varying、boolean、char、character varying、character、varchar、date、double precision、integer、interval、numeric、decimal、real、smallint、time(with or without time zone),timestamp(with or without time zone),xml。

Each data type has an external representation determined by its input and output functions. Many built-in types have obvious formats. However, many types are eitherPostgreSQLunique to (e.g., geometric paths), or may have several different formats (e.g., date and time types). Some input and output functions are irreversible, meaning that the result of the output function may lose precision when compared with the original input.