Last Updated : 14 May, 2025
PostgreSQL is a powerful, open-source relational database management system that supports a wide variety of data types. These data types are essential for defining the nature of the data stored in a database column. which allows developers to define, store, and manipulate data in a way that aligns with the specific needs of their applications.
In this article, We will learn about the PostgreSQL Data Types in detail by understanding various Data Types in PostgreSQL and so on.
PostgreSQL Data TypesIn PostgreSQL, data types are essential for determining the kind of data that can be stored in a database column. Each data type has its own characteristics, such as storage requirements, behavior, and optimization potential. Which helps in optimizing data storage ensuring data integrity and how efficiently data is accessed, stored, and processed. Here, we will look into the various data types available in PostgreSQL. The following data types are supported by PostgreSQL:
1. Numeric TypeThese 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 -10^38 +1 to 10^38 -1 with up to 38 digits of precision. 123.45, -987.654321 numeric(p, s) Similar to decimal, with user-defined precision and scale, representing exact numeric values. Variable -10^38 +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 × 10^38 (max) to -1.701411734 × 10^38 (min). 3.14, -0.001, 2.71828 double precision An 8-byte double precision floating-point number. 8 bytes 1.7976931348623157 × 10^308 (max) to -1.7976931348623157 × 10^308 (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.
'\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 AD2024-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.
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) Conclusion
In conclusion, PostgreSQL's extensive range of data types equips developers with the tools to store and manage various kinds of data effectively. Understanding these data types is crucial for creating efficient database designs, enhancing performance, and ensuring data integrity.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4