How could I get to have a look into LOB contents?

\d ticketgrantingticket

              Table "public.ticketgrantingticket"
          Column           |          Type          | Modifiers 
id                         | character varying(765) | not null
number_of_times_used       | numeric(10,0)          | 
creation_time              | numeric(19,0)          | 
last_time_used             | numeric(19,0)          | 
previous_last_time_used    | numeric(19,0)          | 
ticketgrantingticket_id    | character varying(765) | 
expiration_policy          | oid                    | 
authentication             | oid                    | 
services_granted_access_to | oid                    | 
expired                    | boolean                | 
proxied_by                 | character varying(1)   | 
    "ticketgrantingticket_pkey" PRIMARY KEY, btree (id)

SELECT expiration_policy, 
FROM ticketgrantingticket LIMIT 2;

 expiration_policy | authentication | services_granted_access_to 
           1165742 |        1165743 |                    1165744
           1165757 |        1165758 |                    1165759
(2 rows)

This shows those numbers by default.

I so far found this which I think is not working properly in my client somehow:

SELECT expiration_policy, 
       encode(authentication::bytea, 'escape'),
FROM ticketgrantingticket LIMIT 2;

ERROR:  cannot cast type oid to bytea
LINE 1: SELECT expiration_policy, encode(authentication::bytea, 'esc...

I can see the same in pgAdmin III.

I am expecting blobs there because IINM oid can be used as a large object reference. We are using oid because not knowing what we were doing. I have so far assessed that one of those three columns will be safely and profitably converted to bytea, but am being unable to spy the other two columns' contents in the source (non postgres) system, so I decide to spy them in this (postgres) target system.


2 Answers 2


I ran

SELECT expiration_policy, lo_get(authentication), services_granted_access_to 
FROM ticketgrantingticket

after reading https://www.postgresql.org/docs/current/static/lo-funcs.html suggested by a now defunct comment. That worked.

  • 6
    In PostgreSQL 10 this needs to be e.g. lo_get(authentication::oid)
    – OrangeDog
    Commented Feb 11, 2019 at 12:46

Because this question is nearing closure, I'll paste the links provided by Andriy M and a few more.

Large objects are kind of esoteric. They permit you to seek inside of them. Usually you build systems on top of them, like Raster support in PostGIS. As a side note, psql has some helpers.

\dl                    list large objects, same as \lo_list
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_unlink LOBOID      large object operations

Those are pretty much the functions available as client-side functions functions.

Not the answer you're looking for? Browse other questions tagged or ask your own question.