SQL in a Nutshell, 2nd Edition

PostgreSQL Datatypes

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

BIGSERIAL

See SERIALS.

BIT (SQL2003 Datatype: BIT)

A fixed-length bit string.

BIT VARYING(n), varbit(n) (SQL2003 Datatype: BIT VARYING)

Denotes a variable-length bit string n bits in length.

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),(x2,y2)), signifying the opposite corners of the box (upper-right and lower-left corners, respectively). The outer parentheses are optional.

BYTEA (SQL2003 Datatype: BINARY LARGE OBJECT)

Raw, binary data such ...