Re: Inserting large binary data into lo type table

On 08/12/2014 06:20 AM, 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|\\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E646769
>
2E6775622E75792220786D6C6E733A6E73323D22687474703A2F2F7777772E77332E6F72672F323030302F30392F786D6C64736967232220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F726
> 72F323030312F584D4C536368656D612D696E7374616E6365222076657273696F6E3D22312E3
>
> When runing a simple insert I get:
>
> [postgres@dbfactura01dmz  /tmp]$ psql -d aif_test -U aif <
> subida_postgres_copys_sbox.sql
> Password for user aif:
> ERROR:  invalid input syntax for type oid:
>
\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E6.............
>
> sql script:
> [postgres@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??

I think the type you are looking for is bytea:

http://www.postgresql.org/docs/9.0/interactive/datatype-binary.html

If you really want to use large objects(lo) see here:

http://www.postgresql.org/docs/9.0/interactive/largeobjects.html

>
>
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com