PostgreSQL® Data Types: Mappings to SQL, JDBC, and Java Data Types

This was one of my most popular blogs (no idea why) for a long time, but our marketing people decided to hack it for SEO ratings, so here’s the original…

1. Data Types

A long time ago at university, I learned my first high-level programming language, Pascal, from the book by Niklaus Wirth:

Algorithms + Data Structures = Programs

But I didn’t really learn much about databases studying computer science, as they were taught by another department (business systems). So maybe now’s the time to start with an imaginary book called something like:

Queries + Data Types = Databases?

Data types have a long and important history in computing, driven initially by word lengths and machine data types but becoming more powerful and abstract as computer science matured. One popular 1980’s magazine was even named after a data type (“BYTE”, early microprocessors such as the 8008, Z80, and 6800 were characterized by BYTE/8-bit word sizes, in an era when the PDP-11 had 16-bit words, the VAX had 32-bit words, and the Cray-1 a massive 64-bit word size).

Byte magazine December 1975 with two data types on the cover (BYTE and Character!) (Source: Wikimedia)

2. PostgreSQL Data Types

“Data Types” is a popular PostgreSQL search, so I decided to do some investigation of my own into why they are so important. First of all, why do data types matter in PostgreSQL? Doing some preliminary research I found out that data types in PostgreSQL are important for at least the following aspects (possibly more!):

1. As column data types when creating a table
2. For functions and operators
3. For constraints
4. For creating types and domains, and
5. When using PostgreSQL from a programming language (e.g. PostgreSQL to/from Python, and “C”).

PostgreSQL has a lot of built-in data types that are described in Chapter 8 of the documentation. And you can add new data types, so I guess there are really an infinite number of data types possible.

There’s a table that enumerates at least 43 built-in data types, and reveals that along with the official name some types have aliases (used internally for historical reasons). For example “real” has the alias “float4” (a single precision 4-byte floating-point number).

Here’s the full table which shows the variety of data types available (see https://www.postgresql.org/docs/current/datatype.html#DATATYPE-TABLE )

3. Using PostgreSQL From Java — the PgJDBC Driver

How do you use PostgreSQL from Java? With JDBC! (Java Database Connectivity). There’s a PostgreSQL JDBC Driver (PgJDBC for short) which 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.

It’s easy to download PostgreSQL, install it, and start the database server running. You also need to download the JDBC driver.

Connecting to the database is easy from jdbc:

Connection conn = DriverManager.getConnection(“jdbc:postgresql://localhost/?user=name&password=abc&ssl=false”);

To create tables you need to specify PostgreSQL data types for all the columns. For example, here’s a simple test table with integer id (which is the primary key) and value columns:

CREATE TABLE test1 ( id integer PRIMARY KEY; value integer; );

You can then do simple things in Java/PgJDBC using statements:

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(“INSERT INTO test1 VALUES (1,1000)”);

rs = st.executeQuery(“SELECT * FROM test1 WHERE id = 1”);

while (rs.next())

System.out.print(“Result = “ + rs.getInt(“value”));

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.

5. Mappings From PostgreSQL Data Types to SQL/JDBC Data Types

ok, from here on the original blog is intact, see here https://www.instaclustr.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-data-types/