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.
| DATA TYPE | NARRATION | USAGE |
| 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
