Postgresql – getting insert queries from pgAdmin 4

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

I use pgAdmin 1.14.2 on Ubuntu, but I imagine this will work anywhere else as well.

In pgAdmin, go to Tools -> Backup..., where you'll get a dialog with a few tabs. On the first one (File Options) give a filename and choose the 'Plain' format from the dropdown list. On the next tab (Dump Options #1) tick the 'Use Insert commands' option at the bottom and the 'Only data' option at the top (and don't tick the 'Use Column Inserts' one). On the Objects tab you can choose whichever tables you need then press the Done button.

Now you'll get a file with the INSERT statements for every row - and some other commands as well. You can remove the latter with your favourite editor. Then the list can be sorted - if you want to sort by the first column in the table (that is, more often than not the ID) then you can use a simple sort. If you need a different sorting then you have to apply some magic in an editor.