1

I'm using PostgreSQL 9.5 and trying to import XML data file to database. To load the XML I use this function from here

CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) 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;
$$
LANGUAGE plpgsql;

I successfully load data from a small XML file, but when I try a 4GB XML file, it returns error:

SQL Error [54000]: ERROR: large object read request is too large  
Where: SQL statement "SELECT lo_get(l_oid)" PL/pgSQL function
bytea_import(text) line 6 at SQL statement.

It seems lo_get cannot read/extract very large data. It's said that since version 9.4, PostgreSQL can handle up to 4TB, so how can I import very large XML data file to PostgreSQL 9.5?

Thanks in advance

1

The lo_import is successful, creating a 4GB large object.

But the hard limit for the bytea datatype is 1GB, so these contents are not transferable into a bytea.

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.