Postgresql – vertica/postgres copy text to binary column

blobcopypostgresqlverticaxml

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)

Best Answer

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)

Related Question