How to fetch TEXT column value from postgresql

Question

I have a the following simple table in postgreSQL:

CREATE TABLE data ( id bigint NOT NULL, text_column text, );

The values of the text_column , as I see them in the phpPgAdmin web site, are numbers (long). As I read, postgreSQL keeps a pointer to the actual data. How can I fetch the actual string value of the text_column? Doing:

select text_column from data

returns numbers...

Thanks

Are you ***absolutely*** sure the text_column is defined as `text`? The behaviour you are describing sounds as if the column is defined as `oid` - which is the stupid default behaviour of Hibernate when creating `LOB` columns. Can you copy and paste the output of the `\d+ data` command in `psql`? Make sure you do show us the ***exact*** output, not some kind of "obfuscated" one. — , Aug 28 '12 at 09:05
Checkout this link: http://stackoverflow.com/q/4488693/330315 and this http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html and this http://stackoverflow.com/q/5043992/330315 — , Aug 28 '12 at 09:18

score 55 · Answer 1 · answered May 29 '15 at 12:15

55

Following helped us:

select convert_from(loread(lo_open(value::int, x'40000'::int), x'40000'::int),  'UTF8') from t_field;

where value is field, which contains TEXT, and t_field is obviously name of table.

answered May 29 '15 at 12:15

asm0dey

2,841
2
20
33

2

Worked for me! Thank you. – callafa Apr 29 '16 at 14:09
It's probably obvious but the above command does a SELECT ALL essentially. You can also target individual records by using a where clause. E.g. select convert_from(loread(lo_open(value::int, x'40000'::int), x'40000'::int), 'UTF8') from t_field where id=5; – logixplayer May 19 '20 at 12:42
How to delete those records? I can see the text as you describe but If I truncate or drop the table my DB size is not changed. I really want to delete those records – gunescelil Mar 18 '21 at 13:34
@gunescelil Does VACUUM FULL work for you? – asm0dey Mar 19 '21 at 08:16
Actually and fortunately we are still not on the production with that code. So I deleted all the postgres table and saved my disk. But later I saw some interfaces of large objects. Maybe someone can use those interfaces to delete the records – gunescelil Mar 19 '21 at 08:55

score 3 · Answer 2 · answered Oct 21 '14 at 22:58

3

From psql run \lo_export ID FILE where ID is the number stored in the text column in your table and FILE is the path and filename for the results. The number is a reference to the large object table. You can view its contents by running \lo_list.

answered Oct 21 '14 at 22:58

Najitaka

335
1
4
9

score 3 · Answer 3 · edited Jul 24 '23 at 14:44

3

Provided it's text_column is text, which means it's an oid, this should work too :

select convert_from(lo_get(text_column::oid), 'UTF8') from data;

edited Jul 24 '23 at 14:44

Marco Bolis

1,222
2
15
31

answered May 18 '21 at 13:59

Sebastien

791
5
6

score -2 · Answer 4 · answered Aug 28 '12 at 09:00

-2

Works fine , May be the field values are in numbers:

 > \d+ type

 Column    | Type    

 name      |  text   

 test_id   | integer

select name from type;

  name 

   AAA

answered Aug 28 '12 at 09:00

thar45

3,518
4
31
48

How to fetch TEXT column value from postgresql

4 Answers4

Linked