Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question is not about bytea v. oid v. blobs v. large objects, etc.

I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This can, presumably, be done by one of the PL/ languages, and I may look into doing this with PL/Python in the future.

As I am still testing and experimenting, I would simply like to insert data from a file (on the server) using "standard" SQL statements. I am aware that only administrators with write permission on the server would be able to insert data in the way I would like to. I'm not concerned about that at this stage as users would not be inserting bytea data at present. I have searched the various StackExchange sites, the PostgreSQL Archives and the Internet generally, but have not been able to find an answer.

Edit: This discussion from 2008 implies that what I want to do is not possible. How are bytea fields used then?

Edit: This similar question from 2005 remains unanswered.

Solved: The details provided here on the psycopg website provided the basis for a solution I've written in Python. It may also be possible to insert binary data into a bytea column using PL/Python. I don't know if this is possible using "pure" SQL.

share|improve this question
1  
The link to the psycopg docs is broken and my edit seems to have been rejected(!?). Here is the current location. –  Aryeh Leib Taurog Sep 27 '13 at 7:52
    
@AryehLeibTaurog: Thanks. I rejected the edit because it was not clear to me that your changed text was a hyperlink. If you'd like to make the edit again, I'll approve it. –  SabreWolfy Sep 27 '13 at 19:48

4 Answers 4

up vote 6 down vote accepted

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');
share|improve this answer
    
I have tested this solution and it works. Thanks. –  SabreWolfy Aug 30 '11 at 11:24
    
For the reverse process, I haven't tried this, but if it works, lo_export will be all you need –  Jack Douglas Aug 30 '11 at 12:01
    
Thanks :) I deleted my earlier comment about trying to read out the bytea data, because I realized that this was not part of my original question. I'll check out the links you posted. –  SabreWolfy Aug 30 '11 at 12:07

This solution isn't exactly efficient in terms of runtime, but it's trivially easy compared to making your own headers for COPY BINARY. Further, it doesn't require any libraries or scripting languages outside of bash.

First, convert the file into a hexdump, doubling the size of the file. xxd -p gets us pretty close, but it throws in some annoying newlines that we have to take care of:

xxd -p /path/file.bin | tr -d '\n' > /path/file.hex

Next, import the data in PostgreSQL as a very large text field. This type holds up to one GB per field value, so we should be okay for most purposes:

CREATE TABLE hexdump (hex text); COPY hexdump FROM '/path/file.hex';

Now that our data is a gratuitously large hex string, we use PostgresQL's decode to get it into a bytea type:

CREATE TABLE bindump AS SELECT decode(hex, 'hex') FROM hexdump;
share|improve this answer
    
+1 very clever! –  Gaius Apr 1 '11 at 21:34
    
This solution results in \n characters being removed from the file though. –  SabreWolfy Aug 30 '11 at 11:23
1  
SabreWolfy: No, it doesn't. The tr -d '\n' is operating on the output of xxd, which encodes the binary content of the input as ASCII hexadecimal characters (0-9 and a-f). xxd also happens to output line feeds at regular intervals to make the output human-readable, but in this case we want them removed. Line feeds in the original data will be in hex form, and will remain unaffected. –  goodside Aug 30 '11 at 15:12

The solution above with xxd is nice and, for small files, very fast. The "-p" must be just after xxd, not after the file name.

Below an example script that I'm using.

xxd -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hex echo " -- CREATE TABLE hexdump (hex text); DELETE FROM hexdump; COPY hexdump FROM '/tmp/image.hex';

-- CREATE TABLE bindump (binarydump bytea); DELETE FROM bindump; INSERT INTO bindump (binarydump) (SELECT decode(hex, 'hex') FROM hexdump limit 1);

UPDATE users SET image= (SELECT decode(hex, 'hex') FROM hexdump LIMIT 1) where id=15489 ; " | psql mydatabase

share|improve this answer
    
Good catch on the -p flag. I've updated my answer. –  goodside May 26 '11 at 18:46

Use the Postgres COPY BINARY function. This is broadly equivalent to Oracle's external tables.

share|improve this answer
    
Thanks. The link you gave indicates that the data must either be in ASCII or PostgreSQL's binary table format. Further down the page, mention is made that the binary table format is first created with a COPY TO command. Would either of these approaches allow me to insert a binary file (PDF, document, spreadsheet) into a bytea column? –  SabreWolfy Mar 14 '11 at 20:56
    
The PostgreSQL documentation on COPY BINARY (postgresql.org/docs/8.4/interactive/sql-copy.html) indicates that a special file header is required when inserting binary data. Do I need to build this header and append it to binary data? That does seem somewhat complex for simply storing a string of binary data. –  SabreWolfy Mar 14 '11 at 21:00
    
Hmm, now that you mention it I am not sure, I just remembered the command and assumed it would do that. Perhaps PL/whatever is the only way to do it. –  Gaius Mar 14 '11 at 21:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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