source: https://www.tutorialspoint.com/postgresql/postgresql_c_cpp.htm

Installation

The the latest version of libpqxx is available to be downloaded from the link Download Libpqxx. So download the latest version and follow the following steps −

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

Before you start using C/C++ PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line −

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

You can start/restart postgres server in case it is not running using the following command −

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

C/C++ Interface APIs

The following are important interface routines which can sufice your requirement to work with PostgreSQL database from your C/C++ program. If you are looking for a more sophisticated application then you can look into the libpqxx official documentation, or you can use commercially available APIs.

S. No.API & Description
1pqxx::connection C( const std::string & dbstring )This is a typedef which will be used to connect to the database. Here, dbstring provides required parameters to connect to the datbase, for example dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432.If connection is setup successfully then it creates C with connection object which provides various useful function public function.
2C.is_open()The method is_open() is a public method of connection object and returns boolean value. If connection is active, then this method returns true otherwise it returns false.
3C.disconnect()This method is used to disconnect an opened database connection.
4pqxx::work W( C )This is a typedef which will be used to create a transactional object using connection C, which ultimately will be used to execute SQL statements in transactional mode.If transaction object gets created successfully, then it is assigned to variable W which will be used to access public methods related to transactional object.
5W.exec(const std::string & sql)This public method from transactional object will be used to execute SQL statement.
6W.commit()This public method from transactional object will be used to commit the transaction.
7W.abort()This public method from transactional object will be used to rollback the transaction.
8pqxx::nontransaction N( C )This is a typedef which will be used to create a non-transactional object using connection C, which ultimately will be used to execute SQL statements in non-transactional mode.If transaction object gets created successfully, then it is assigned to variable N which will be used to access public methods related to non-transactional object.
9N.exec(const std::string & sql)This public method from non-transactional object will be used to execute SQL statement and returns a result object which is actually an interator holding all the returned records.

Connecting To Database

The following C code segment shows how to connect to an existing database running on local machine at port 5432. Here, I used backslash \ for line continuation.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      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;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

Now, let us compile and run the above program to connect to our database testdb, which is already available in your schema and can be accessed using user postgres and password pass123.

You can use the user ID and password based on your database setting. Remember to keep the -lpqxx and -lpq in the given order! Otherwise, the linker will complain bitterly about the missing functions with names starting with “PQ.”

$g++ test.cpp -lpqxx -lpq
$./a.out
Opened database successfully: testdb

Create a Table

The following C code segment will be used to create a table in previously created database −

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      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;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

When the above given program is compiled and executed, it will create COMPANY table in your testdb database and will display the following statements −

Opened database successfully: testdb
Table created successfully

INSERT Operation

The following C code segment shows how we can create records in our COMPANY table created in above example −

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      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;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

When the above given program is compiled and executed, it will create given records in COMPANY table and will display the following two lines −

Opened database successfully: testdb
Records created successfully

That’s as far as i was able to go during my quick bout with postgres interfacing using C++ on linux.
I had to adjust the pg_hba.conf file to connect using the compiled binary.
The Next section i was unable to compile due to linking during compile, or some other reason.

Here’s the output / error:

root@home]# g++ ConnectandSelect.cpp -lpqxx ConnectandSelect.cpp -lpq
ConnectandSelect.cpp: In function ‘int main(int, char)’: ConnectandSelect.cpp:20:11: warning: deprecated conversion from string constant to ‘char*’ [-Wwrite-strings] sql = “SELECT * from COMPANY”; ^ ConnectandSelect.cpp: In function ‘int main(int, char)’:
ConnectandSelect.cpp:20:11: warning: deprecated conversion from string constant to ‘char*’ [-Wwrite-strings]
sql = “SELECT * from COMPANY”;
^
/tmp/ccE0ll75.o: In function main': ConnectandSelect.cpp:(.text+0x9b): multiple definition ofmain’
/tmp/ccaVVSi4.o:ConnectandSelect.cpp:(.text+0x9b): first defined here
/tmp/ccaVVSi4.o: In function main': ConnectandSelect.cpp:(.text+0x250): undefined reference topqxx::tuple::operator const’
ConnectandSelect.cpp:(.text+0x2a5): undefined reference to pqxx::tuple::operator[](int) const' ConnectandSelect.cpp:(.text+0x319): undefined reference topqxx::tuple::operator const’
ConnectandSelect.cpp:(.text+0x36e): undefined reference to pqxx::tuple::operator[](int) const' ConnectandSelect.cpp:(.text+0x3df): undefined reference topqxx::tuple::operator const’
/tmp/ccaVVSi4.o: In function pqxx::const_result_iterator::const_result_iterator(pqxx::result const*, unsigned long)': ConnectandSelect.cpp:(.text._ZN4pqxx21const_result_iteratorC2EPKNS_6resultEm[_ZN4pqxx21const_result_iteratorC5EPKNS_6resultEm]+0x33): undefined reference topqxx::tuple::tuple(pqxx::result const*, unsigned long)’
/tmp/ccE0ll75.o: In function main': ConnectandSelect.cpp:(.text+0x250): undefined reference topqxx::tuple::operator const’
ConnectandSelect.cpp:(.text+0x2a5): undefined reference to pqxx::tuple::operator[](int) const' ConnectandSelect.cpp:(.text+0x319): undefined reference topqxx::tuple::operator const’
ConnectandSelect.cpp:(.text+0x36e): undefined reference to pqxx::tuple::operator[](int) const' ConnectandSelect.cpp:(.text+0x3df): undefined reference topqxx::tuple::operator const’
collect2: error: ld returned 1 exit status


Update: compilation completed after commenting out the C.disconnect line, we don’t need that object in later versions of libpqxx.

Tips: 1. Use sequencing if running the program more than once to avoid primary key conflict.