1. Introduction
In this tutorial, we’ll explore the common PSQLException error: “column is of type json but the expression is of type character varying” when using JPA to interact with PostgreSQL. We’ll explore why this error occurs, identify common scenarios that trigger it, and demonstrate how to resolve it.
2. Common Causes
In PostgreSQL, the JSON or JSONB data types are used to store JSON data. However, if we attempt to insert a string (character varying) into a column that expects JSON, PostgreSQL throws the “column is of type json but expression is of type character varying” error. This is especially common when working with JPA and PostgreSQL, as JPA may try to save a string to a JSON column, leading to this error.
3. Demonstrating the Error
We’ll create a basic Spring Boot project with the necessary dependencies and test data to demonstrate the error. First, we need to add the PostgreSQL dependencies to our Maven pom.xml file:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.1</version>
<scope>runtime</scope>
</dependency>
Next, we create a JPA entity class that maps to the student table:
@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String admitYear;
@Column(columnDefinition = "json")
private String address;
// getters and setters
}
In this entity class, the address field is mapped to the address column in the student table. Notably, we’ve specified the columnDefinition attribute as JSON to indicate that this column is of type JSON.
Now, let’s try to save a Student object to the database:
Student student = new Student();
student.setAdmitYear("2024");
student.setAddress("{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");
Throwable throwable = assertThrows(Exception.class, () -> studentRepository.save(student));
assertTrue(ExceptionUtils.getRootCause(throwable) instanceof PSQLException);
In this code, we’ve created a Student object and set the address field to a JSON string. Then, we save this object to the database using the save() method of the studentRepository object.
However, this results in a PSQLException:
Caused by: org.postgresql.util.PSQLException: ERROR: column "address" is of type json but expression is of type character varying
This error occurs because JPA tries to save a string to a JSON column, which isn’t allowed.
4. Using @Type Annotation
To fix this error, we need to handle JSON types correctly. We can use the @Type annotation provided by the hibernate-types library. First, let’s add the hibernate-types dependency to our pom.xml:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>2.18.0</version>
</dependency>
Next, we update the entity to include the @TypeDef and @Type annotations:
@Entity
@Table(name = "student_json")
@TypeDefs({
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class StudentWithTypeAnnotation {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String admitYear;
@Type(type = "jsonb")
@Column(columnDefinition = "json")
private String address;
// Getters and Setters
}
Here, @TypeDef(name = “jsonb”, typeClass = JsonBinaryType.class) registers a custom type named JSONB which uses the JsonBinaryType class from the hibernate-types-52 library. JsonBinaryType handles PostgreSQL’s JSONB data type, allowing JSON data to be stored and retrieved as JSONB efficiently.
The @Type annotation is used to specify a custom Hibernate type for a field. *By specifying @Type(type = “jsonb”), we tell Hibernate to use the custom type JSONB registered via @TypeDef.* This custom type handles the conversion between Java objects and JSONB data in PostgreSQL.
This setup ensures that JSON data is efficiently stored and retrieved in PostgreSQL using the JSONB data type:
StudentWithTypeAnnotation student = new StudentWithJson();
student.setAdmitYear("2024");
student.setAddress("{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");
studentWithTypeAnnotationRepository.save(student);
StudentWithTypeAnnotation retrievedStudent = studentWithTypeAnnotationRepository.findById(student.getId()).orElse(null);
assertThat(retrievedStudent).isNotNull();
assertThat(retrievedStudent.getAddress()).isEqualTo("{\"postCode\":\"TW9 2SF\",\"city\":\"London\"}");
5. Native Query
Moreover, when we insert JSON data into a PostgreSQL table using the @Query annotation with a native SQL query, we’ll encounter the same error. Let’s demonstrate this error by creating an insert native query:
@Query(value = "INSERT INTO student (admit_year, address) VALUES (:admitYear, :address) RETURNING *", nativeQuery = true)
Student insertJsonData(@Param("admitYear") String admitYear, @Param("address") String address);
When we call this method with a JSON string, we’ll expect to get an exception:
Throwable throwable = assertThrows(Exception.class, () ->
studentRepository.insertJsonData("2024","{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}"));
assertTrue(ExceptionUtils.getRootCause(throwable) instanceof PSQLException);
To resolve it, we need to cast the JSON string to JSONB type before inserting it to avoid this error. Here’s an example of how to do it:
public interface StudentWithTypeAnnotationRepository extends JpaRepository<StudentWithTypeAnnotation, Long> {
@Query(value = "INSERT INTO student (admit_year, address) VALUES (:admitYear, CAST(:address AS JSONB)) RETURNING *", nativeQuery = true)
StudentWithTypeAnnotation insertJsonData(@Param("admitYear") String admitYear, @Param("address") String address);
}
In the above code, we use the CAST(:address AS JSONB) syntax to cast the :address parameter to JSONB type. Now, let’s test this method:
StudentWithTypeAnnotation student = studentWithJsonRepository.insertJsonData("2024","{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");
StudentWithTypeAnnotation retrievedStudent = studentWithJsonRepository.findById(student.getId()).orElse(null);
assertThat(retrievedStudent).isNotNull();
assertThat(retrievedStudent.getAddress()).isEqualTo("{\"city\": \"London\", \"postCode\": \"TW9 2SF\"}");
6. Conclusion
In this article, we’ve explored how to address the PSQLException error “column is of type json but the expression is of type character varying” that arises when using JPA to map Java objects to PostgreSQL JSON columns.
By using the @Type annotation and casting the JSON string to JSONB type when using native SQL queries, we can efficiently store and retrieve JSON data in PostgreSQL using the JSONB data type.
As always, the source code for the examples is available over on GitHub.