Like all database systems, PostgreSQL
lets you store data using a variety of different data types. These data types
allow the database engine to optimize its use of memory and storage, and to
perform operations on the stored data more efficiently and with fewer errors.

Data type selection plays an important role in how
efficiently your RDBMS functions, and so it’s important to be fully aware of
the options available to you, and to select the most appropriate data type for
your storage needs. That’s where this document comes in. It outlines the most
important data types supported by PostgreSQL, describing when and how each
should be used, and provides you with a ready supply of choices the next time
you sit down to optimize your existing databases or create new ones.

Table A

Data Type

Description

Bytes Used

Recommended Use
BOOLEAN

Logical
true/false

1

Storing attributes that
can only take one of three possible values: true, false and NULL.

Examples: Enable/disable, yes/no fields
SMALLINT

Integer
values in the range -32000 to +32000 (appx)

2

Storing relatively small
integer values.

Examples: Age, quantity
INTEGER

Integer
values in the range -2000000000 to +2000000000 (appx)

4

Storing medium integer
values.

Example: Distance
BIGINT

Extremely
large integer values that do not fit into either SMALLINT or INTEGER fields

8

Storing large integer
values.

Example: Scientific/mathematical values
FLOAT

Floating-point
values, with precision up to 6 digits

4

Storing decimal values

Examples: Measurement, temperature

NUMERIC

Floating-point
values with user-defined precision

Variable

Storing decimal values
which require high precision

Examples: Currency amounts, scientific values
SERIAL

Auto-incrementing
positive integer values

4

Automatically numbering
data sets, like table records

Example: Table primary keys
CHAR

Fixed-length
strings

4 + specified
string length

Storing string values
which will always contain a preset number of characters.

Examples: Airline, country or post codes
VARCHAR

Variable-length
strings, with a preset maximum limit

Variable; 4
+ actual string length

Storing string values of
varying length (up to a specified maximum limit).

Examples: Names, passwords, short text labels
TEXT

Variable-length
strings with no maximum limit

Variable

Storing large blocks of
textual data

Examples: News stories, product descriptions
BYTEA

Binary
strings

Variable; 4
+ actual string length

Storing binary data

Examples: Images, attachments, binary documents
DATE

Date
values in the format yyyy-mm-dd

4

Storing dates

Examples: Birthdays, product expiry dates

TIME

Time
values in the format hh:mm:ss

8

 

Storing times

Example: Alarms
TIMESTAMP

Combined
date and time values in the format yyyy-mm-ddhh:mm:ss

8

 

Recording time instants

Examples: Event triggers, “last log-in”
timestamps
INTERVAL

Interval
values

12

 

Storing durations

Examples: Interval between two timestamps, task
start/end times
OID

PostgreSQL
Object IDentifiers

4

Identifying table records

Example: Table primary keys

For a complete list and detailed descriptions, see the PostgreSQL manual.