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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
@Andriy_M Why do you think that "This edit deviates from the original intent of the post." (The edit done by informatik01?) – miracle173 Aug 19 '15 at 10:07
@miracle173: Because I got the impression that some of the suggested tags were irrelevant (well, just one actually, blob). If that was a mistake, I sincerely apologise. – Andriy M Aug 19 '15 at 10:56
up vote 17 down vote accepted

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
  l_oid oid;
  r record;
  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 ||;
  end loop;
  perform lo_unlink(l_oid);


insert into my_table(bytea_data) select bytea_import('/my/');
share|improve this answer
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

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
This solution results in \n characters being removed from the file though. – SabreWolfy Aug 30 '11 at 11:23
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

Use pg_read_file('location_of file')::bytea.

For example,

create table test(id int, image bytea);
insert into test values (1, pg_read_file('/home/xyz')::bytea);
share|improve this answer

The answer with xxd is nice and, for small files, very fast. Below is 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

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 ( 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

protected by Community Jun 9 '15 at 17:07

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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