title: Inserting a file into a Postgres bytea column using perl/SQL
body: <p>I'm working with a legacy system and need to find a way to insert files into a pre-existing Postgres 8.2 bytea column using Perl. </p>
  
  <p>So far my searching has lead me to believe the following:</p>
  
  <ol>
  <li>there is no consensus best approach for this.</li>
  <li>lo_import looks promising, but I'm apparently too perl-tarded to get it to work.</li>
  </ol>
  
  <p>I was hoping to do something like the following</p>
  
  <pre><code>my $bind1 = "foo"
  my $bind2 = "123"
  my $file = "/path/to/file.ext"
  
  my $q = q{
        INSERT INTO generic_file_table
          (column_1,
           column_2,
           bytea_column
          ) 
        VALUES
        (?, ?, lo_import(?))
  };
  my $sth = $dbh-&gt;prepare($q);
  $sth-&gt;execute($bind1, $bind2, $file);
  $sth-&gt;finish();`
  </code></pre>
  
  <p>My script works w/o the lo_import/bytea part. But with it I get this error:</p>
  
  <blockquote>
    <p>DBD::Pg::st execute failed: ERROR:  column "contents" is of type bytea but expression is >of type oid at character 176
       HINT:  You will need to rewrite or cast the expression.</p>
  </blockquote>
  
  <p>What I think I'm doing wrong is that I'm not passing the actual binary file to the DB properly. I think I'm passing the file path, but not the file itself. If that's true then what I need to figure out is how to open/read the file into a tmp buffer, and then use the buffer for the import. </p>
  
  <p>Or am I way off base here? I'm open to any pointers, or alternative solutions as long as they work with Perl 5.8/DBI/PG 8.2.</p>