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. — user330315
– user330315, Commented Aug 28, 2012 at 9:05
Checkout this link: stackoverflow.com/q/4488693/330315 and this shredzone.de/cilla/page/299/… and this stackoverflow.com/q/5043992/330315 — user330315
– user330315, Commented Aug 28, 2012 at 9:18

asm0dey · Accepted Answer · 2015-05-29 12:15:39Z

59

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, 2015 at 12:15

asm0dey

2,9492 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

logixplayer Over a year ago

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;

gunescelil Over a year ago

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

asm0dey Over a year ago

@gunescelil Does VACUUM FULL work for you?

gunescelil Over a year ago

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

Marco Bolis · Accepted Answer · 2023-07-24 14:44:17Z

4

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, 2023 at 14:44

Marco Bolis

1,2192 gold badges15 silver badges33 bronze badges

answered May 18, 2021 at 13:59

Sebastien

8916 silver badges6 bronze badges

Comments

Najitaka · Accepted Answer · 2014-10-21 22:58:18Z

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, 2014 at 22:58

Najitaka

3451 gold badge4 silver badges9 bronze badges

Comments

thar45 · Accepted Answer · 2012-08-28 09:00:19Z

-3

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, 2012 at 9:00

thar45

3,5705 gold badges35 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to fetch TEXT column value from postgresql

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related