Top 12 PostgreSQL® data types and how data types work in PgJDBC

What Is a data type in PostgreSQL?

A data type in PostgreSQL defines the kind of data that can be stored in a table column, function argument, or variable. It determines the set of valid values and the operations that can be performed on those values.

PostgreSQL includes a wide range of built-in data types, such as numeric types (like integer, decimal, and float), character types (text, varchar, and char), date/time types (timestamp, date, and interval), and boolean. It also supports more complex types like arrays, JSON, and geometric data.

Each data type has specific rules for storage size, allowed values, and behavior in expressions. For example, the integer type stores whole numbers using 4 bytes, while the text type can hold variable-length strings.

In addition to built-in types, PostgreSQL allows users to define custom data types using the CREATE TYPE command. This feature supports more complex or domain-specific data modeling needs.

PostgreSQL data types cheat sheet

Understanding Key PostgreSQL Data Types

1. Numeric types

PostgreSQL offers several numeric types to handle different precision and storage needs. The primary types include smallint (2 bytes), integer or int (4 bytes), and bigint (8 bytes) for whole numbers. For floating-point numbers, PostgreSQL provides real (4 bytes) and double precision (8 bytes).

For exact numeric values, especially important in financial or scientific applications, PostgreSQL supports the numeric and decimal types. These can store numbers with a user-defined precision and scale, avoiding rounding errors associated with floating-point types. Arithmetic operations on numeric types maintain precision but can be slower than integer operations.

2. Monetary type

The money type is used for representing currency values. It stores amounts with a fixed fractional precision, using 8 bytes. The display format is locale-sensitive, which can include currency symbols and formatting.

While convenient for applications dealing with prices or totals, the money type is limited in flexibility compared to numeric. It may be affected by locale settings, and arithmetic operations require care to avoid rounding errors. For maximum precision and control, using numeric with appropriate constraints is often preferred.

3. Character/string types

PostgreSQL supports several character types: char(n), varchar(n), and text. The char(n) type pads strings with spaces to a fixed length, which can lead to unexpected behavior. varchar(n) enforces a maximum length but does not pad the input. text is a variable-length type without a length limit, and is often the preferred choice due to its flexibility and performance.

Internally, all these types are stored efficiently, and performance differences are minimal in most cases. Constraints on string length can be enforced using check constraints instead of fixed-length types.

4. Binary data type

The bytea type stores binary data as a sequence of bytes. It’s suitable for handling non-text files such as images, audio, or other binary formats. Data stored in bytea must be properly escaped when inserted and retrieved, typically using functions like encode and decode.

PostgreSQL also supports large objects (LOBs) for very large binary data, but bytea is easier to use when binary content fits comfortably in memory and doesn’t exceed typical row size limits.

5. Boolean data type

The boolean type represents truth values: TRUE, FALSE, and NULL. It takes 1 byte of storage. Valid input literals include true, false, t, f, yes, no, 1, and 0. Booleans are useful for status flags or binary choices in applications. They can be used directly in conditions, enabling concise query expressions and logic.

6. Date/time types

PostgreSQL includes comprehensive date and time support through types like date, time, timestamp, and interval. The date type stores calendar dates, while time represents the time of day without a date. timestamp can include both date and time, with or without time zone support.

The interval type is used to represent a duration of time, such as days, months, or hours. All these types support robust arithmetic operations and formatting functions, making them powerful for scheduling, logging, and time-based analysis.

7. Enumerated (ENUM) types

ENUM types allow defining a list of valid string values for a column. This is useful for fields with a fixed set of possible options, such as status values (‘draft‘, ‘published‘, ‘archived‘). PostgreSQL enforces the allowed values at the database level, improving data integrity.

Once defined with CREATE TYPE, the order of ENUM values is preserved and can be used for sorting. However, adding or removing values requires altering the type, which can be more restrictive than using a separate lookup table.

8. Geometric types

PostgreSQL includes built-in support for two-dimensional geometric data through types like point, line, lseg (line segment), box, path, polygon, and circle. These types can store spatial coordinates and shapes directly in table columns.

They support operations such as containment, intersection, and distance calculations. Though not a full spatial database, PostgreSQL’s geometric types are useful for simple spatial queries without the need for extensions like PostGIS.

9. Network address types

PostgreSQL provides specific types for storing and querying IP-related data: cidr for network blocks, inet for individual IP addresses (IPv4 or IPv6), and macaddr for MAC addresses. These types support input validation, address comparisons, and subnet operations.

They are particularly useful in applications dealing with networking, logging, and access control, allowing compact storage and efficient querying of network data.

10. Bit string types

Bit string types, bit(n) and bit varying(n), store sequences of bits. bit(n) has a fixed length, while bit varying(n) allows variable-length bit strings up to a defined limit.

These types are used for storing binary flags, masks, or compact representations of data. Bitwise operations such as AND, OR, XOR, and shifts are supported, making them useful for low-level data manipulation.

11. Text search types

PostgreSQL supports full-text search through two main types: tsvector and tsquery. A tsvector stores a document in a searchable format, while tsquery represents a search query. These types enable fast and efficient text searching using indexes such as GIN.

Text search in PostgreSQL includes features like stemming, stop-word filtering, and ranking. This makes it suitable for search functionalities in applications like blogs, wikis, or product catalogs.

12. Special Postgres data types

PostgreSQL also includes several domain-specific types. Examples include:

• uuid for universally unique identifiers
• xml for storing XML documents
• json and jsonb for semi-structured data
• range types (like int4range, tsrange) for representing continuous ranges of values
• hstore for storing sets of key-value pairs

These types support specialized indexing and querying capabilities, making PostgreSQL adaptable to diverse application needs without requiring external tools.

Understanding data types in PgJDBC—PostgreSQL with Java

The PostgreSQL JDBC Driver (PgJDBC for short) allows Java programs to connect using standard, database independent, Java code. It’s an open source Pure Java (Type 4, which talks native PostgreSQL protocol) driver and is well documented. Data types work a bit differently in PgJDBC. Let’s see how it works.

Simple Statements and Prepared Statements

For simple Statements, the INSERT statement is just a string, so you don’t need a real data type yet. However, to extract the returned value you do need to know the PostgreSQL type, and also the corresponding Java data type if you are going to do anything more useful than just print it out (i.e. store it, process it):

However, to use Prepared Statements you need to use the correct data types (lines 2, 3, 6, and 9) for both INSERT and SELECT, otherwise you’ll get a run time SQL exception:

Note that rs.getString(“value”) also seems to work ok for the basic data types (i.e. automatic casting), which is a feature we’ll find useful below.

Mappings from PostgreSQL data types to SQL/JDBC data types

Mappings from shape to color (Source: Wikimedia)

But how do you know which Java data types the PostgreSQL data types actually map to in advance? The getTypeInfo() method in the java.sql.DatabaseMetaData Interface is an interface implemented by driver vendors to let users know the capabilities of the database in combination with the JDBC driver. The getTypeInfo() method retrieves a description of all the data types supported by the database, ordered by DATA_TYPE and then by how closely they map to the corresponding JDBC SQL type.

Running this reveals the complete list of PostgreSQL data types and their mapping to SQL/JDBC Data types, for a total of 183 data types. Removing all the (many) OTHER and ARRAY (e.g. _record etc) data types leaves us with this more manageable table:

Table 2: Mappings from PostgreSQL to SQL/JDBC data types

Note that the PostgreSQL data types are using the aliases from Table 1. But this is only part of the answer. How do we know what Java data types correspond to the SQL data types?

Mappings from SQL/JDBC data types to Java data types

Based on the JDBC specifications, here are Java object types mapped to JDBC types.

Table 3: Complete mappings from PostgreSQL to SQL/JDBC to Java data types

Notes:

• REF_CURSOR doesn’t appear in the jdbc appendices, but is mentioned in section “13.3.3.4 REF Cursor Support” of the specification, and may map to Types.REF_CURSOR.
• _abc stands for one of many ARRAY data types available in PostgreSQL (_record to _yes_or_no).

Type conversion

So now we know the correct Java types to use, everything should just magically work correctly right? Well, mostly, as long as you remember what the PostgreSQL data types used in the table columns are, and use the correct PostgreSQL to Java mappings. As an experiment, I tried a few data types including int2/int4/int8 (short, int, long), char/text (String), and numeric (BigDecimal). The main thing to watch out for is conversion/casting between different sized types—going from longer to short types results in run-times errors (if you are lucky) or truncation (if you are unlucky). How about a really simple type such as bit, bool, and boolean? That should be idiot proof? True? (or False or Unknown…). Let’s try and see what happens (in my next PostgreSQL blog)!

Conclusions

We’ve really only started to scratch the surface of PostgreSQL data types, as PostgreSQL allows for custom data types, user defined types, arrays, and OTHER data types (e.g. JSON). I was also surprised to discover that PostgreSQL is really an ORDBMS (an object-relational DB), and has support for some object-oriented features such as table inheritance and function overloading. How these all work from JDBC would be more than enough to fill my imaginary database book, or several actual (but currently chimerical) future blogs.

Reliable, scalable, and hassle-free database management for PostgreSQL deployments

Instaclustr for PostgreSQL empowers businesses with a reliable, scalable, and high-performance database solution designed to simplify data management. This fully managed service relieves users of time-consuming administrative tasks like backups, updates, and monitoring, so they can focus on what truly matters—driving business forward.

Instaclustr provides the benefits of enterprise-grade reliability and performance without the complexities of managing infrastructure. The Instaclustr expert team ensures PostgreSQL clusters are optimized for high availability, scalability, and security. Whether a surge in online activity or operations need to scale to meet growing demands, Instaclustr provides the stability and flexibility needed to keep applications running seamlessly.

Instaclustr for PostgreSQL also delivers unmatched transparency through a flat-rate pricing model and clear SLAs. Additionally, the Instaclutr customer support team is available 24/7 to troubleshoot issues and offer guidance, ensuring database operations are always in expert hands. Instaclustr for PostgreSQL achieves operational efficiency, unlocking the full potential of data.

For more information:

• Best managed PostgreSQL options: Top 5 solutions in 2025
• PostgreSQL® is now available in PCI mode for the NetApp Instaclustr Platform
• Scaling PostgreSQL®: Challenges, tools, and best practices