PostgreSQL – How to Insert File Data into a Bytea Column

blobdatafileplpgsqlpostgresql

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.

Best Answer

as superuser:

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

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 $$
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');
Related Question