Why can I not access columns by name?

Question

Sample code:

pqxx::connection c("user=postgres");
pqxx::work txn(c);
pqxx::result r = txn.exec("SELECT d.datname as \"Name\","
       "pg_catalog.pg_get_userbyid(d.datdba) as \"Owner\","
       "pg_catalog.pg_encoding_to_char(d.encoding) as \"Encoding\","
       "d.datcollate as \"Collate\","
       "d.datctype as \"Ctype\","
       "pg_catalog.array_to_string(d.datacl, E'\\n') AS \"Access privileges\""
       "   FROM pg_catalog.pg_database d"
       "   ORDER BY 1");
try {
  for(int rownum=0; rownum<r.size(); ++rownum ) {
    const pqxx::result::tuple row = r[rownum];
    std::cout << "Column 0 Name: \'" << row[0].name() << "\': " << row[0].c_str() << std::endl;
    const std::string s = "Name";
    std::cout << (row[0].name() == s) << std::endl;
    std::cout << "dbname: \'" << row[s].c_str() << "\'" << std::endl; // Exception here
  }
} catch(const pqxx::argument_error &e) {
  std::cout << "Argument Error: " << e.what() << std::endl;
}

My output is as follows

Column 0 Name: 'Name': mydb
1
Argument Error: Unknown column name: 'Name'

The first columns name is "Name" and testing the rows name against the string "Name" produces a true statement. But when I access it by that string, i get an exception.

Answer 1

Ok, completely asinine. You have to quote the name if you want things like uppercase to work.

So changing

const std::string s = "Name";

to

const std::string s = "\"Name\"";

fixes the problem. Apparently, the C interface lowercases what you call it, but doesn't lowercase the fields its testing against. ARG! I had to go through the libpqxx code to figure that out.