How to use perform a select on table under schema in postgres Database?

Question

I'm trying to perform a select operation on postgres DB table and I'm getting below error:

[etb@centos etbtest]$ ./a.out

Opened database successfully: ETBDB
ERROR:  relation "etb_reference.etb_member" does not exist
LINE 1: SELECT * FROM etb_reference.ETB_MEMBER

how do we refer the schema name in libpq++ exec function?

I tried escaping the schema name with other escape options like ",',\ etc but it didn't help.

my code :

   try {
      connection C("dbname = ETBDB user = etbadm password = etbtest \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

       work wrk(C);

   result res = wrk.exec("SELECT * FROM etb_reference.ETB_MEMBER");

   for (
      pqxx::result::const_iterator row = res.begin();
      row != res.end();
      ++row)
    {

     std::cout
        << row["MEMBER_ID"].as<int>() << "\t"
        << row["SYS_CRE_DATE"].as<std::string>() << "\t"
        << row["SYS_UPD_DATE"].as<std::string>() << "\t"
        << row["MEMBER_CS"].as<std::string>() << "\t"
        << row["MEMBER_TD"].as<std::string>() << "\t"
        << row["MEMBER_ASD"].as<std::string>() << "\t"
        << row["MEMBER_ITM"].as<std::string>() << "\t"
        << std::endl;

    }
C.disconnect ();
 return 0;

   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

I can execute set search_path to 'schema' but I have divided by database into schemas like application tables, reference tables, configuration tables for easy management of porting and maintaining via automated shell and db scripts.

Answer 1

Opened database successfully: ETBDB ERROR: relation "etb_reference.etb_member" does not exist LINE 1: SELECT * FROM etb_reference.ETB_MEMBER

The error message contains the table name in lower cases. It also prints the line where the error occurs, and this line contains the table name in upper case.

SELECT * FROM etb_reference."ETB_MEMBER"

When you query by hand, you use upper case and put the table name between double quotes.

PostgreSQL works with lower case by default, unless the name is between double quotes, so to fix the issue, simply put the table name between double quotes in the query (or, better, only use lower case names)