0

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)

3
  • Please don't add the Postgres tag, if you are using Vertica. Those are two very different database products. You will end up with answers for Postgres that you can't use in Vertica. Don't add unrelated DBMS just to attract more people. Mar 5, 2020 at 11:28
  • vertica & postgers ate using the same dialect a sulotion for postgres can help me as well.
    – NoamiA
    Mar 5, 2020 at 13:01
  • Well, there is no varbinary in Postgres. And why don't you use the xml data type to store XML values? Mar 5, 2020 at 13:09

1 Answer 1

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.