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.