PostgreSQL is a powerful open-source database that supports a wide range of data types. These data types define the kind of data stored in each column and help applications store and manage information correctly.
They are used to:
- Define data format for each column.
- Store different kinds of values like numbers, text, and dates.
- Process and manipulate data accurately.
- Support application-specific data needs.
PostgreSQL provides different types of data types, which are as follows:
1. Numeric Type
These types are used for storing numerical data. PostgreSQL provides various numeric types, including:
| Numeric Type | Description | Storage Size | Range | Example Values |
|---|---|---|---|---|
| smallint | A 2-byte integer type. | 2 bytes | -32,768 to 32,767 | -100, 0, 20000 |
| integer | A 4-byte integer type (default integer type). | 4 bytes | -2,147,483,648 to 2,147,483,647 | -5000, 0, 150000 |
| bigint | An 8-byte integer type. | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | -1000000000, 0, 30000000000 |
| decimal(p, s) | A numeric type with user-defined precision (p) and scale (s). | Variable | -1038 +1 to 1038 -1 with up to 38 digits of precision. | 123.45, -987.654321 |
| numeric(p,s s) | Similar to decimal, with user-defined precision and scale, representing exact numeric values. | Variable | -1038 +1 to 10^38 -1 with up to 38 digits of precision. | 123.456, -12345.6789 |
| real | A 4-byte single precision floating-point number. | 4 bytes | 1.701411734 × 1038 (max) to -1.701411734 × 1038 (min). | 3.14, -0.001, 2.71828 |
| double precision | An 8-byte double precision floating-point number. | 8 bytes | 1.7976931348623157 × 10308 (max) to -1.7976931348623157 × 10308 (min). | 3.141592653589793, -1.0E-5 |
| serial | An auto-incrementing 4-byte integer type, often used for primary keys. | 4 bytes | Same as integer. | Automatically increments by 1 |
| bigserial | An auto-incrementing 8-byte integer type, used for large-scale primary keys. | 8 bytes | Same as bigint. | Automatically increments by 1 |
2. Monetary Type
Here’s a detailed overview of the monetary types available in PostgreSQL, presented in a tabular format:
| Monetary Type | Description | Storage Size | Range | Example Values |
|---|---|---|---|---|
| money | A fixed-point type used for storing currency values. | 8 bytes | -2,147,483,648.00 to 2,147,483,647.00 | $1234.56, -$10.00 |
3. Character Types
Here’s a detailed overview of the character types available in PostgreSQL, presented in a tabular format:
| Character Type | Description | Storage Size | Maximum Length | Example Values |
|---|---|---|---|---|
| char(n) | A fixed-length character type. If the string is shorter than n, it is padded with spaces. | n + 1 bytes (1 byte per character plus 1 byte for length) | 1 to 8,000 | 'A', 'Hello ' (padded) |
| varchar(n) | A variable-length character type. It can store strings with a maximum length of n. | 1 + actual length (1 byte for length) | 1 to 8,000 | 'Alice', 'Bob' |
| text | A variable-length character type with no specific length limit. | 1 + actual length (1 byte for length) | No limit | 'This is a long text string.' |
4. Binary Types
Binary types in PostgreSQL are used to store raw binary data. The bytea type is specifically designed for this purpose, allowing you to store byte arrays for applications that need to manage non-text data, such as images, audio, and other multimedia files.
| Binary Type | Description | Storage Size | Maximum Length | Example Values |
|---|---|---|---|---|
| bytea | Stores binary data (byte array). | Variable | No limit | '\xDEADBEEF', '\x00FF' |
5. Date/Time Types
PostgreSQL provides several date and time types to handle temporal data effectively. These types are essential for applications that require accurate time tracking, such as event scheduling, logging, and historical data analysis.
| Date/Time Type | Description | Storage Size | Range | Example Values |
|---|---|---|---|---|
| date | Stores calendar dates (year, month, day). | 4 bytes | 4713 BC to 5874897 AD | 2024-10-08 |
| time | Stores time of day (hour, minute, second). | 8 bytes | 00:00:00 to 24:00:00 (with or without time zone) | 13:45:30, 12:00:00 |
| timestamp | Stores both date and time (without time zone). | 8 bytes | 4713 BC to 5874897 AD | 2024-10-08 14:30:00 |
| timestamptz | Stores both date and time (with time zone). | 8 bytes | 4713 BC to 5874897 AD | 2024-10-08 14:30:00-05 |
| interval | Represents a span of time (days, hours, minutes, seconds). | Variable | -178000 years to 178000 years | 1 day, 2 hours 30 mins |
6. Boolean Types
Boolean types in PostgreSQL store truth values which can be either true or false. The boolean type is simple yet powerful for conditional checks and decision-making in applications.
| Boolean Type | Description | Storage Size | Example Values |
|---|---|---|---|
| boolean | Stores true, false, and null values. | 1 byte | true, false, NULL |
7. Enumerated Types
Enumerated types (enums) allow us to define a data type with a static, ordered set of values. This is useful for situations where a column must contain one of a limited set of predefined values enhancing data integrity and readability.
| Enumerated Type | Description | Storage Size | Example Values |
|---|---|---|---|
| enum | A user-defined type that consists of a static, ordered set of values. | Variable | 'small', 'medium', 'large' |
8. Geometric Types
Geometric types in PostgreSQL are used to store two-dimensional geometric data. These types are ideal for applications involving spatial data, such as geographic information systems (GIS), computer graphics, and geometric modeling.
| Geometric Type | Description | Storage Size | Example Values |
|---|---|---|---|
| point | Represents a point in a 2D space (x, y). | 16 bytes | (1, 2), (3.5, 4.5) |
| line | Represents an infinite line in 2D space. | 32 bytes | line(1, 2) |
| lseg | Represents a line segment in 2D space. | 32 bytes | lseg((1, 2), (3, 4)) |
| box | Represents a rectangular box in 2D space. | 32 bytes | box((1, 2), (3, 4)) |
| path | Represents a path (open or closed) in 2D space. | Variable | path((1, 2), (3, 4), (5, 6)) |
| polygon | Represents a polygon in 2D space. | Variable | polygon((1, 1), (2, 2), (1, 2)) |
| circle | Represents a circle in 2D space (center, radius). | 32 bytes | circle((1, 1), 5) |
9. Other (Special Purpose) Data Types
PostgreSQL also provides several special-purpose data types that are used for modern applications, networking, and document-based data.
| Data Type | Description | Storage Size | Example |
|---|---|---|---|
| UUID | Stores 128-bit universally unique identifiers | 16 bytes | 550e8400-e29b-41d4-a716-446655440000 |
| JSON | Stores JSON data as text | Variable | {"name":"Riya","age":21} |
| JSONB | Stores JSON in binary optimized format | Variable | {"price":120,"stock":true} |
| INET | Stores IPv4 or IPv6 addresses | 7 or 19 bytes | 192.168.1.1 |
| CIDR | Stores network blocks | 7 or 19 bytes | 192.168.0.0/24 |
| MACADDR | Stores MAC addresses | 6 bytes | 08:00:2b:01:02:03 |
| TSVECTOR | Stores full-text search documents | Variable | 'postgres database' |