in4mation ... organized

Database optimization with the power of PostgreSQL data types

Article Posted by Jishar Ibrahim for www.wisdombay.com

This article exemplifies you the most imperative data types supported by PostgreSQL, telling when and how each type should be used in your operations.

Like all other database systems, PostgreSQL lets you to store data using a range of different data types. These data types allow the database engine to optimize its use of memory and storage, and also it performs various operations on the stored data more efficiently, effectively with fewer errors.

Data type selection plays an important role in how efficiently and effectively your RDBMS functions and so it's important to be fully aware of the options available in the particular database implementation and to select the most appropriate data type for your storage needs. That's where this article helps you. This articles is focusing on most important data types supported by PostgreSQL and describing when and how each data type should be used and provides you with a ready supply of alternatives the next time you sit down to optimize your existing databases or create new ones.

TEXT Variable-length strings with no maximum limit Storing large blocks of textual data Examples: News stories, product descriptions
VARCHAR Variable-length strings, with a preset maximum limit

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

Examples: Names, passwords, short text labels

CHAR Fixed-length strings Storing string values which will always contain a preset number of characters. Examples: Airline, country or post codes
SERIAL Auto incrementing positive integer values Automatically numbering data sets, like table records. Example: Table primary keys
NUMERIC Floating-point values with user-defined precision Storing decimal values which require highprecision. Examples: Currency amounts, scientific values
FLOAT Floating-point values, with precision up to 6 digits Storing decimal values Examples: Measurement, temperature
BIGINT Extremely large integer values that do not fit into either SMALLINT or INTEGER fields Storing large integer values. Example: Scientific/mathematical values
INTEGER Integer values in the range - 2000000000 to +2000000000 (approximately) Storing medium integer values. Example: Distance
SMALLINT Integer values in the range -32000 to +32000 (approximately) Storing relatively small integer values.
Examples: Age, quantity
BOOLEAN Logical true/false Storing attributes that can only take one of three possible values: true, false and NULL. Examples: Enable/disable, yes/no fields
BYTEA Binary strings Storing binary data
Examples: Images,attachments, binary documents
DATE Date values in the format yyyy-mm-dd Storing dates
Examples: Birthdays, product expiry dates
TIME Time values in the format hh:mm:ss Storing times
Example: Alarms
TIMESTAMP Combined date and time values in the format yyyy-mm-dd hh:mm:ss Recording time instants Examples: Event triggers, "last log-in" timestamps
INTERVAL Interval values Storing durations
Examples: Interval between two timestamps, task start/end times
OID PostgreSQL Object Identifiers Identifying table records Example: Table primary keys

For a complete list of the entire PostgreSQL data types and its detailed descriptions, visit the PostgreSQL manual.
Please post your comments to jishar@wisdombay.com