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.


6 Answers 6


as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
  l_oid oid;
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);

lo_get was introduced in 9.4 so for older versions you would need:

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 || r.data;
  end loop;
  perform lo_unlink(l_oid);


insert into my_table(bytea_data) select bytea_import('/my/file.name');
  • For the reverse process, I haven't tried this, but if it works, lo_export will be all you need Commented Aug 30, 2011 at 12:01

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);


  • 15
    Or a little simpler, pg_read_binary_file('/path/to/file'). See postgresql.org/docs/current/static/functions-admin.html Commented Jun 10, 2017 at 22:17
  • Caveat: you need to be superuser and it can only be used for relative paths over data folder
    – Whimusical
    Commented Jul 23, 2021 at 16:30
  • it worth to mention that file have to reside in postgres data_directory on the server for pg_read_file function to work
    – Eugene
    Commented Aug 22, 2023 at 8:30

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;
  • This solution results in \n characters being removed from the file though.
    – SabreWolfy
    Commented Aug 30, 2011 at 11:23
  • 2
    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
    Commented Aug 30, 2011 at 15:12
  • This one liner created a valid insert statement for me: HEX="$(xxd -p /c/path_to/empty.zip | tr -d '\n')"; echo "INSERT INTO table (bincolumn) VALUES (decode('$HEX', 'hex'));" Commented Mar 23, 2023 at 16:14

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

Here is how to do it without superuser privileges (such as on Heroku).

\lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf'
update contracts set contract = lo_get(:LASTOID) where id = 77;

You can use \lo_list to see the large objects, and \lo_unlink to delete them. The contract field in my example is bytea.

     Column      |            Type             |
contract         | bytea                       |
  • nice and easy, you can also pass the number returned by lo_import to lo_get
    – Thom
    Commented Jun 24 at 21:36

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

  • 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
    Commented Mar 14, 2011 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
    Commented Mar 14, 2011 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
    Commented Mar 14, 2011 at 21:05

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