# Top 12 PostgreSQL® data types and how data types work in PgJDBC [Blog](/blog/)>[Technology](/blog/category/technical/)>Top 12 PostgreSQL® data types and how data types work in PgJDBC Top 12 PostgreSQL® data types and how data types work in PgJDBC =============================================================== May 24, 2021 | By [ Paul Brebner](https://www.instaclustr.com/blog/author/paul-brebner/) [ ](https://x.com/intent/tweet?text=Top%2012%20PostgreSQL%C2%AE%20data%20types%20and%20how%20data%20types%20work%20in%20PgJDBC&url=https://www.instaclustr.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-data-types/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://www.instaclustr.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-data-types/&title=&summary=Top%2012%20PostgreSQL%C2%AE%20data%20types%20and%20how%20data%20types%20work%20in%20PgJDBC&source=) 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. *This is part of a series of articles about [PostgreSQL](https://www.instaclustr.com/education/postgresql/complete-guide-to-postgresql-features-use-cases-and-tutorial/)* PostgreSQL data types cheat sheet --------------------------------- **Category****Type(s)****Description****Storage (Typical)****Numeric**`smallint`, `integer`, `bigint`Whole numbers with increasing range and storage size2–8 bytes`real`, `double precision`Approximate floating-point numbers4–8 bytes`numeric`, `decimal`Exact numbers with user-defined precisionVariable**Monetary**`money`Currency values with fixed precision, locale-aware display8 bytes**Character/String**`char(n)`, `varchar(n)`, `text`Fixed or variable-length strings; `text` is most flexibleVariable**Binary**`bytea`Raw binary data (e.g., images, files)Variable**Boolean**`boolean`Logical `true`/`false`/`NULL` values1 byte**Date/time**`date`, `time`, `timestamp`, `interval`Dates, times, timestamps (with/without time zone), and time durations4–8 bytes**ENUM**User-definedFixed list of string values4 bytes**Geometric**`point`, `line`, `polygon`, etc.2D geometric shapes and coordinatesVariable**Network address**`cidr`, `inet`, `macaddr`IP networks, IP addresses, and MAC addresses6–20 bytes**Bit String**`bit(n)`, `bit varying(n)`Fixed or variable-length sequences of bitsVariable**Text search**`tsvector`, `tsquery`Full-text search documents and queriesVariable**Special types**`uuid`, `xml`, `json`, `jsonb`, `range`, `hstore`Specialized data: identifiers, documents, semi-structured data, ranges, key-value pairsVariableSee a full list of supported data types [here](https://www.postgresql.org/docs/current/datatype.html#DATATYPE-TABLE). ### 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 [PostgreSQL performance](https://www.instaclustr.com/education/postgresql/postgresql-performance-factors-and-7-ways-to-supercharge-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](https://jdbc.postgresql.org/) (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): int value = rs.getInt("value"); 1 int value = rs.getInt("value"); 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: PreparedStatement pst1 = conn.prepareStatement("INSERT INTO test1(id, value) VALUES (?, ?)"); <strong>pst1.setInt(1, 2);</strong> <strong>pst1.setInt(2, 2000);</strong> int rowsInserted = pst1.executeUpdate(); PreparedStatement pst2 pst = conn.prepareStatement("SELECT \* FROM test1 WHERE id = ?"); <strong>pst2.setInt(1, 2);</strong> rs = pst2.executeQuery(); while (rs.next()) <strong>int value = rs.getInt("value");</strong> 1 2 3 4 5 6 7 8 9 PreparedStatement pst1 = conn.prepareStatement("INSERT INTO test1(id, value) VALUES (?, ?)"); <strong>pst1.setInt(1, 2);</strong> <strong>pst1.setInt(2, 2000);</strong> int rowsInserted = pst1.executeUpdate(); PreparedStatement pst2 pst = conn.prepareStatement("SELECT \* FROM test1 WHERE id = ?"); <strong>pst2.setInt(1, 2);</strong> rs = pst2.executeQuery(); while (rs.next()) <strong>int value = rs.getInt("value");</strong> 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 colour]() Mappings from shape to color [*(Source: Wikimedia)*](https://commons.wikimedia.org/w/index.php?curid=20802095) 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. rs = conn.getMetaData().getTypeInfo()) while (rs.next()) System.out.println(rs.getString("TYPE\_NAME") + "\\t" + JDBCType.valueOf(rs.getInt("DATA\_TYPE")).getName()); 1 2 3 4 rs = conn.getMetaData().getTypeInfo()) while (rs.next()) System.out.println(rs.getString("TYPE\_NAME") + "\\t" + JDBCType.valueOf(rs.getInt("DATA\_TYPE")).getName()); 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: **PostgreSQL data type****SQL data type**`bool``BIT``bit``BIT``int8``BIGINT``bigserial``BIGINT``oid``BIGINT``bytea``BINARY``char``CHAR``bpchar``CHAR``numeric``NUMERIC``int4``INTEGER``serial``INTEGER``int2``SMALLINT``smallserial``SMALLINT``float4``REAL``float8``DOUBLE``money``DOUBLE``name``VARCHAR``text``VARCHAR``varchar``VARCHAR``date``DATE``time``TIME``timetz``TIME``timestamp``TIMESTAMP``timestamptz``TIMESTAMP``cardinal_number``DISTINCT``character_data``DISTINCT``sql_identifier``DISTINCT``time_stamp``DISTINCT``yes_or_no``DISTINCT``xml``SQLXML``refcursor``REF_CURSOR`**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](https://jcp.org/en/jsr/detail?id=221), here are Java object types mapped to JDBC types. **PostgreSQL data type****SQL/JDBC data type****Java type**`bool``BIT``boolean``bit``BIT``boolean``int8``BIGINT``long``bigserial``BIGINT``long``oid``BIGINT``long``bytea``BINARY``byte[]``char``CHAR`String`bpchar``CHAR`String`numeric``NUMERIC``java.math.BigDecimal``int4``INTEGER``int``serial``INTEGER``int``int2``SMALLINT``short``smallserial``SMALLINT``short``float4``REAL``float``float8``DOUBLE``double``money``DOUBLE``double``name``VARCHAR`String`text``VARCHAR`String`varchar``VARCHAR`String`date``DATE``java.sql.Date``time``TIME``java.sql.Time``timetz``TIME``java.sql.Time``timestamp``TIMESTAMP``java.sql.Timestamp``timestamptz``TIMESTAMP``java.sql.Timestamp``cardinal_number``DISTINCT`Mapping of underlying type`character_data``DISTINCT`Mapping of underlying type`sql_identifier``DISTINCT`Mapping of underlying type`time_stamp``DISTINCT`Mapping of underlying type`yes_or_no``DISTINCT`Mapping of underlying type`xml``SQLXML``java.sql.SQLXML``refcursor``REF_CURSOR`Undefined`_abc``ARRAY``java.sql.array`**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](https://www.postgresql.org/docs/current/sql-createtype.html), [user defined types](https://www.postgresql.org/docs/current/xtypes.html), [arrays](https://www.postgresql.org/docs/current/arrays.html), and `OTHER` data types (e.g. [JSON](https://www.postgresql.org/docs/13/datatype-json.html)). 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](https://www.postgresql.org/docs/current/ddl-inherit.html) and [function overloading](https://www.postgresql.org/docs/current/xfunc-overload.html). 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](https://www.instaclustr.com/education/postgresql/best-managed-postgresql-options-top-6-solutions-in-2026/) - [PostgreSQL® is now available in PCI mode for the NetApp Instaclustr Platform](https://www.instaclustr.com/blog/postgresql-is-now-available-in-pci-mode-for-the-netapp-instaclustr-platform/) - [Scaling PostgreSQL®: Challenges, tools, and best practices](https://www.instaclustr.com/education/postgresql/scaling-postgresql-challenges-tools-and-best-practices/) ### About the author **[Paul Brebner](https://www.instaclustr.com/blog/author/paul-brebner/)** | Technology Evangelist at Instaclustr Paul has extensive R&D and consulting experience in distributed systems, technology innovation, software architecture, and engineering, software performance and scalability, grid and cloud computing, and data analytics and machine learning. [ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com) ![mail icon]()#### Get the latest articles for open sourceIn your inbox Sign up now ### Related content [ Zero Downtime Migration to Instaclustr Yes, we can migrate existing Cassandra clusters to Instaclustr without any downtime. Here's what to expect from the process... ](https://www.instaclustr.com/blog/zero-downtime-migration-to-instaclustr/) [ Workflow Comparison: Uber Cadence vs Netflix Conductor When choosing what’s right for your company’s opensource workflow needs it is important to know the difference and similarities ... ](https://www.instaclustr.com/blog/workflow-comparison-uber-cadence-vs-netflix-conductor/) [ Will Your Cassandra Database Project Succeed?: The New Stack Open source Apache Cassandra® continues to stand out as an enterprise-proven solution for organizations seeking high availability... ](https://www.instaclustr.com/blog/will-your-cassandra-database-project-succeed-the-new-stack/) ×Sign upto ourNewsletter -----------------------