PostgreSQL provides a wide range of data types that define the kind of data that can be stored in table columns. They help maintain data accuracy, integrity and consistency.
- Improve data accuracy and maintain data integrity.
- Support advanced data types such as JSON, arrays and user-defined types for application-specific needs.
Numeric Type
These types are used for storing numerical data. PostgreSQL provides various numeric types, including:
| Name | Description | Range |
|---|---|---|
| smallint | 2-byte integer | -32,768 to 32,767 |
| integer | 4-byte integer | -2,147,483,648 to 2,147,483,647 |
| bigint | 8-byte integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| decimal(p,s) | Exact numeric value with precision and scale | -10³⁸ + 1 to 10³⁸ - 1 |
| numeric(p,s) | Exact numeric value with precision and scale | -10³⁸ + 1 to 10³⁸ - 1 |
| real | 4-byte floating-point number | Approx. -3.4 × 10³⁸ to 3.4 × 10³⁸ |
| double precision | 8-byte floating-point number | Approx. -1.8 × 10³⁰⁸ to 1.8 × 10³⁰⁸ |
| serial | Auto-incrementing integer | Same as integer |
| bigserial | Auto-incrementing large integer | Same as bigint |
Monetary Type
Here’s a detailed overview of the monetary types available in PostgreSQL, presented in a tabular format:
| Name | Description | Range |
|---|---|---|
| money | Fixed-point type used for storing currency values. | -2,147,483,648.00 to 2,147,483,647.00 |
Character Types
Here’s a detailed overview of the character types available in PostgreSQL, presented in a tabular format:
| Name | Description | Range |
|---|---|---|
| char(n) | Fixed-length character type. | 1 to 8,000 |
| varchar(n) | Variable-length character type with a maximum length of n. | 1 to 8,000 |
| text | Variable-length character type with no specific length limit. | No limit |
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.
| Name | Description | Range |
|---|---|---|
| bytea | Stores binary data as a byte array. | No limit |
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.
| Name | Description | Range |
|---|---|---|
| date | Stores calendar dates. | 4713 BC to 5874897 AD |
| time | Stores time of day. | 00:00:00 to 24:00:00 |
| timestamp | Stores date and time without time zone. | 4713 BC to 5874897 AD |
| timestamptz | Stores date and time with time zone. | 4713 BC to 5874897 AD |
| interval | Represents a span of time. | -178,000 to 178,000 years |
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.
| Name | Description | Range |
|---|---|---|
| boolean | Stores true, false or NULL values. | true, false, NULL |
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.
| Name | Description | Range |
|---|---|---|
| enum | User-defined type with a fixed set of values. | User-defined values |
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.
| Name | Description | Range |
|---|---|---|
| point | Represents a point in 2D space. | (x, y) |
| line | Represents an infinite line. | line(x, y) |
| lseg | Represents a line segment. | Two points |
| box | Represents a rectangular box. | Two points |
| path | Represents an open or closed path. | Multiple points |
| polygon | Represents a polygon. | Multiple points |
| circle | Represents a circle with center and radius. | (center, radius) |
Other (Special Purpose) Data Types
PostgreSQL also provides several special-purpose data types that are used for modern applications, networking and document-based data.
| Name | Description | Range |
|---|---|---|
| UUID | Stores 128-bit universally unique identifiers. | 128-bit |
| JSON | Stores JSON data as text. | Variable |
| JSONB | Stores JSON data in binary-optimized format. | Variable |
| INET | Stores IPv4 or IPv6 addresses. | IPv4/IPv6 |
| CIDR | Stores network blocks. | IPv4/IPv6 network |
| MACADDR | Stores MAC addresses. | 6 bytes |
| TSVECTOR | Stores full-text search documents. | Variable |
