vertica/postgres copy text to binary column

Question

Until now I used Oracle to store xml file in blob data type column.

Is there any option in Vertica to copy xml file into binary column?

I saw there is varbinary data type but, I haven't succeeded to load this file into one row in this column.

If someone is familiar with this process in PostgreSQL can maybe be helpful (share your knowledge)

Answer 1

As you asked for it: here is a Postgres solution if you can upload all the files to the Postgres server (or at least a directory accessible from there).

do
$$
declare 
  l_rec record;
  l_data bytea;
  -- change this to the server directory where all XML files are stored
  l_dir text := '/data/import';
  l_fname text;
begin
  for l_rec in select * from pg_ls_dir(l_dir) as t(name) loop
    l_fname := l_dir ||'/'||l_rec.name;
    l_data := pg_read_binary_file(l_fname);

    insert into the_table (the_xml_column) 
    values (l_data);
  end loop;
end;
$$

This must be run as the superuser (postgres) because the functions pg_ls_dir() and pg_read_binary_file() have restricted access (for security reasons)