Re: Inserting large binary data into lo type table

Jose Moreira wrote:

> I guess this is easy a question for the gurus, but afraid I cannot get te answer!
>
> I have this table:
>
> aif_test=# \d sbox;
> Table "public.sbox"
> Column | Type | Modifiers
> --------+------------------------+-----------
> id | character varying(255) | not null
> data | lo |
> Indexes:
> "sbox_pkey" PRIMARY KEY, btree (id)
>
>
> And trying to insert a binary file which I show only the first lines:
>
> Emitidas/10001.doc|\\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C
> 41434B43464520786D6C6E733D22687474703A2F2F6366652E646769
> 2E6775622E75792220786D6C6E733A6E73323D22687474703A2F2F7777772E77332E6F72672F323030302F30392F786D6C6473
> 6967232220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F726
> 72F323030312F584D4C536368656D612D696E7374616E6365222076657273696F6E3D22312E3
>
> When runing a simple insert I get:
>
> [postgres(at)dbfactura01dmz /tmp]$ psql -d aif_test -U aif < subida_postgres_copys_sbox.sql
> Password for user aif:
> ERROR: invalid input syntax for type oid:
> \x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C
> 6E733D22687474703A2F2F6366652E6.............
>
> sql script:
> [postgres(at)dbfactura01dmz /tmp]$ more subida_postgres_copys_sbox.sql
> COPY sbox FROM '/tmp/TXT/SBOX.txt' WITH DELIMITER '|' NULL '';
>
> I am not able to insert a simple binary text into a "lo" type column!!! Read on the doc but it should
> work thoug. Any help please??

You must be using the "lo" extension.

Reading the doc I see that "lo" is just an alias for "oid", so that is not the way to
insert a large object.

You need to either import a file with lo_import() or use lo_create() and lowrite() to
create a large object. See http://www.postgresql.org/docs/current/static/largeobjects.html .

The extension essentially contains a trigger that you can use to automatically
delete the large object if the row gets deleted in the table.

Maybe you should go for the "bytea" data type, that would work as you expect.

Yours,
Laurenz Albe