PostgreSQL : PostgreSQL Data Types

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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.