SQL in a Nutshell, 3rd Edition

PostgreSQL Datatypes

The PostgreSQL database supports most SQL2003 datatypes, plus an extremely rich set of datatypes that store spatial and geometric data. PostgreSQL sports a rich set of operators and functions especially for the geometric datatypes, including capabilities such as rotation, finding intersections, and scaling. It also supports additional versions of existing datatypes that are smaller and take up less disk space than their corresponding primary datatypes. For example, PostgreSQL offers several variations on INTEGER to accommodate small or large numbers and thereby consume proportionally less or more space. Here’s a list of the datatypes it supports:

BIGINT, INT8 (SQL2003 datatype: none)

Stores signed or unsigned 8-byte integers within the range of −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

BIGSERIAL

See SERIALS.

BIT (SQL2003 datatype: BIT)

Stores a fixed-length bit string.

BIT VARYING(n), VARBIT(n) (SQL2003 datatype: BIT VARYING)

Stores a variable-length bit string whose length is denoted by n.

BOOL, BOOLEAN (SQL2003 datatype: BOOLEAN)

Stores a logical Boolean (true/false/unknown) value. The keywords TRUE and FALSE are preferred, but PostgreSQL supports the following valid literal values for the “true” state: TRUE, t, true, y, yes, and 1. Valid “false” values are: FALSE, f, false, n, no, and 0.

BOX( (x1, y1), (x2, y2) ) (SQL2003 datatype: none)

Stores the values of a rectangular box in a 2D plane. Values are stored in 32 bytes and are represented as ( (x1, y1), ...