3

This question already has an answer here:

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)   | 
Indexes:
    "ticketgrantingticket_pkey" PRIMARY KEY, btree (id)

SELECT expiration_policy, 
       authentication, 
       services_granted_access_to 
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'),
       services_granted_access_to 
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.

marked as duplicate by joanolo, Andriy M, Paul White May 27 '17 at 19:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

I ran

SELECT expiration_policy, lo_get(authentication), services_granted_access_to 
FROM ticketgrantingticket
LIMIT 2;

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

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

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_list
\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.