How to insert file data into a bytea postgres column?

1.2K    Asked by AnilJha in AWS , Asked on Oct 6, 2022

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.

Answered by Amit verma

To insert file data into a bytea postgres column - 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');


Your Answer

Interviews

Parent Categories