title: How to export full-text files with SQL?
body: <p>There are <strong>an easy way to import/export full-text fields as files?</strong></p>
  
  <ul>
  <li><p>that solve the problem of "load as multiple lines".<br/> Trying with SQL's <a href="https://www.postgresql.org/docs/current/static/sql-copy.html" rel="nofollow noreferrer">COPY</a> I can only to transform full-file into full-table, not into a single text field, because each line from COPY is a raw.</p></li>
  <li><p>that solve the save-back problem, to save the full XML file in the filesystem, without changes in bynary representation (<strong>preserving SHA1</strong>), and without other exernal procedures (as Unix <code>sed</code> use).</p></li>
  </ul>
  
  <p>The main problem is on <em>export</em>, so this is the title of this page.</p>
  
  <p>PS: the "proof of same file" in the the round trip &mdash; import,  export back and compare with original  &mdash; can be obtained by <code>sha1sum</code> demonstration; see examples below. So, a natural demand is also to check same SHA1 by SQL, avoiding to export on simple check tasks.</p>
  
  <hr>
  
  <h2>All examples</h2>
  
  <ol>
  <li><p><strong>Import a full text</strong> into a full-table (is not what I need), <br/> and test that can <strong>export as the same</strong> text. <br/>PS: I need to import one file into one field and one row.</p></li>
  <li><p>Transform <strong>full table into one file</strong> (is not what I need)<br/> and test that can <strong>export as same</strong> text.<br/>PS: I need one row (of one field) into one file.</p></li>
  <li><p>Calculate the  hash by SQL, the <strong>SHA1 of the field</strong>. <br/>Must be <strong>the same</strong> when compare ... Else it is not a solution for me. </p></li>
  </ol>
  
  <p>The folowing examples show each problem and a non-elegant workaround.</p>
  
  <p><strong>1. Import</strong></p>
  
  <pre><code>CREATE TABLE ttmp (x text);
  COPY ttmp FROM '/tmp/test.xml' ( FORMAT text ); -- breaking lines lines
  COPY (SELECT x FROM ttmp) TO '/tmp/test_back.xml' (format TEXT);
  </code></pre>
  
  <p>Checking that original and "back" have exactly the same content:</p>
  
  <pre><code>sha1sum /tmp/test*.*
    570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test_back.xml
    570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test.xml
  </code></pre>
  
  <p>PS: seems perfect, but the problem here is the use of many rows. A real import-solution can import a file into a one-row (and one field). A real export-solution is a SQL function that produce <code>test_back.xml</code> from a single row (of a single field). </p>
  
  <p><strong>2. Transform full table into one file</strong> </p>
  
  <p>Use it to store XML:</p>
  
  <pre><code>CREATE TABLE xtmp (x xml);
  INSERT INTO  xtmp (x) 
    SELECT array_to_string(array_agg(x),E'\n')::xml FROM ttmp
  ;
  COPY (select x::text from xtmp) TO '/tmp/test_back2-bad.xml' ( FORMAT text );
  </code></pre>
  
  <p>... But not works as we can check by <code>sha1sum /tmp/test*.xml</code>, <strong>not produce the same result</strong> for <code>test_back2-bad.xml</code>.</p>
  
  <p>So do also a translation from <code>\n</code> to chr(10), using an external tool (perl, sed or any other) <br/><code>perl -p -e 's/\\n/\n/g'  /tmp/test_back2-bad.xml  &gt; /tmp/test_back2-good.xml</code></p>
  
  <p>Ok, now <code>test_back2-good.xml</code> have the same hash ("570b13fb..." in my example) tham original. 
  Use of Perl is a workaround, how to do without it?</p>
  
  <p><strong>3. The SHA1 of the field</strong></p>
  
  <pre><code>SELECT encode(digest(x::text::bytea, 'sha1'), 'hex') FROM xtmp;
  </code></pre>
  
  <p>Not solved, is not the same hash tham original (the "570b13fb..." in my example)... Perhaps the <code>::text</code> enforced internal representation with <code>\n</code> symbols, so a solution will be direct cast to <code>bytea</code>, but it is an invalid cast. The other workaround also not is a solution,</p>
  
  <pre><code>SELECT encode(digest( replace(x::text,'\n',E'\n')::bytea, 'sha1' ), 'hex') 
  FROM xtmp
  </code></pre>
  
  <p>... I try <code>CREATE TABLE btmp (x bytea)</code> and <code>COPY btmp FROM '/tmp/test.xml' ( FORMAT binary )</code>, but error ("unknown COPY file signature").</p>