Get TEXT column value in psql

Question

I have created simple entity with Hibernate with @Lob String field. Everything works fine in Java, however I am not able to check the values directly in DB with psql or pgAdmin.

Here is the definition from DB:

=> \d+ user_feedback
                    Table "public.user_feedback"
 Column |  Type  | Modifiers | Storage  | Stats target | Description 
--------+--------+-----------+----------+--------------+-------------
 id     | bigint | not null  | plain    |              | 
 body   | text   |           | extended |              | 
Indexes:
    "user_feedback_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

And here is that I get from select:

=> select * from user_feedback;
 id | body  
----+-------
 34 | 16512
 35 | 16513
 36 | 16514
(3 rows)

The actual "body" content is for all rows "normal" text, definitely not these numbers.

How to retrieve actual value of body column from psql?

@Lob is an annotation that saves the data as clob or blob, speaking of java it should be an array of bytes or chars, therefore you are saving some info about that array on the field in postgresql I do not know which value. Try to change your annotation to string equivalent. It should work. See a reference here: http://www.concretepage.com/hibernate/lob-hibernate-annotation — Jorge Campos, Mar 22 '15 at 18:42

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

This will store the content of LOB 16512 in file out.txt :

\lo_export 16512 out.txt

Although using @Lob is usually not recommended here (database backup issues ...). See store-strings-of-arbitrary-length-in-postgresql for alternatives.

edited May 23 '17 at 12:22

Community

1
1

answered Mar 22 '15 at 18:48

Nytux

358
1
9

thanks, I can get to "my content" :) I tried to used generic solution, however I will consider your suggestion since I don't expect to change postgresql soon. – sodik Mar 22 '15 at 18:55

score 1 · Answer 2 · edited May 23 '17 at 11:43

Hibernate is storing the values as out-of-line objects in the pg_largeobject table, and storing the Object ID for the pg_largeobject entry in your table. See PostgreSQL manual - large objects.

It sounds like you expected inline byte array (bytea) storage instead. If so, you may want to map a byte[] field without a @Lob annotation, rather than a @Lob String. Note that this change will not be backward compatible - you'll have to export your data from the database then drop the table and re-create it with Hibernate's new definition.

The selection of how to map your data is made by Hibernate, not PostgreSQL.

See related:

Get TEXT column value in psql

2 Answers2