Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.

What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details. The fact that has been down for the past few days isn't helping me...

How would I go about inserting the contents of somefile.bin into a table with a BYTEA column?

What I have is along these lines:

pqxx::work work( conn );
work.exec( "CREATE TABLE test ( name varchar(20), data BYTEA )" );
work.exec( "INSERT INTO test( name, data ) VALUES ( 'foo', <insert filename.bin here> )" );

If it makes a difference, I'd like to use the new hex format for BYTEA available in PostgreSQL 9.1.

share|improve this question
This may help you:… And please, don't cross-post your questions. – dezso Apr 17 '13 at 4:57
The two libraries have similar names -- libpq and libpqxx -- but the first is C, while libpqxx is C++. The API is very different. – Stéphane Apr 21 '13 at 0:12
up vote 1 down vote accepted

Took a while, but I figured out how to do it. Here is some example code:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
    std::string name = "foo";
    void * bin_data = ...; // -- obviously do what you need to get the binary data...
    size_t bin_size = 123; // -- ...and the size of the binary data

    pqxx::binarystring blob( bin_data, bin_size );
    pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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