JPA with Postgres anomalously writes text in @Lob column as a number

Question

I am trying to use a @Lob column with a Java String type to map its content to TEXT inside Postgres. Here is the relevant entity:

@Entity(name="metadata")
public class Metadata {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "created_on")
    @ColumnDefault(value="CURRENT_TIMESTAMP")
    @Generated(GenerationTime.INSERT)
    private LocalDateTime createdOn;

    @Lob
    @Column(name = "content")
    private String content;

    @Column(name = "draft")
    private Boolean draft;

    @OneToMany(cascade = javax.persistence.CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "metadata")
    private List<Attachment> attachments;

    public void addAttachment(Attachment attachment) {
        if (attachments == null) {
            attachments = new ArrayList<>();
        }
        attachments.add(attachment);
        attachment.setMetadata(this);
    }

    // getters and setters
}

I have code which creates a new Metadata entity based on use input. I verify manually in IntelliJ debug mode that this entity has the content set to its intended value (which happens to be a JSON string). However, when I check Postgres after running the code, I see this data:

my_db=> select * from metadata;
 id | content |       created_on        | draft
----+---------+-------------------------+-------
  1 | 49289   | 2021-04-26 14:21:25.733 | t
(1 row)

Note carefully that the strange values 49289 is appearing where we would expect to see a JSON string. Note that I also verified from the command line that the correct table is what was created:

CREATE TABLE scarfon_attachment (
    id bigint NOT NULL,
    contents text,
    filename character varying(255),
    scarfon_id bigint NOT NULL
);

All the other columns in the entity/table are working as expected. What could be the problem with the @Lob annotation. For reference, I am running a fairly old version of Postgres (9.2), but it is not that ancient.

Answer 1

My first doubt here owed to many sources suggesting multiple ways for creating a TEXT column. For example, this Baeldung post suggests using @Lob in addition to use a definition with the @Column annotation.

As it turns out, @Lob is not interpreted the same by all databases. In the case of Postgres, just using @Lob alone will result in Postgres storing the column contents in a different table, with the column annotated with @Lob just storing an ID for each entry in that table. While it has been suggested here that also specifying the correct type via the @Type annotation can remedy this problem, I decided to go with the second suggestion by the Baledung post, which is using @Column:

@Lob
@Column(columnDefinition="TEXT")
private String content;

This worked fine, and the resulting Postgres table had a TEXT definition as expected. The only potential issue with the above might center around portability to other SQL databases which perhaps do not support a TEXT type, or maybe support some alternative. I did not test beyond Postgres and H2, but in both cases the above was working without issues.

Answer 2

When using @Lob, Hibernate ORM will expect a column of type text in the database with which you can save a text file up to a 1 Gb.

You can avoid using it and it will change the type of the column to VARCHAR(255). The difference is that you can limit the size of a VARCHAR and the default is 255 with ORM, but you cannot limit the size of a text.

You can also change the default column type (I think varchar can be as big as 1Gb):

@Column(name = "content", columnDefinition = "varchar(500)")
String content

So, in the end, which approach to use is up to you. I'm assuming that for big strings text is better but you need to evaluate your use case.

Answer 3

When data is stored in a LOB column, it is not stored directly in the regular row data like other columns. Instead, it is stored in a separate location in the database, and the row only contains a reference (usually a numeric identifier) to that separate location.

The reason you see numbers like 1124 instead of the real TEXT code when you query the database directly is that the actual TEXT content is stored in a separate location, and the number 1124 is a reference (or pointer) to that location. This is a way to manage and optimize the storage of large data that would not fit well within the regular row data structure.

When you retrieve the data using the application, the JPA will handle the process of retrieving the LOB data and mapping it back to the layout field as a string containing the actual TEXT.