Documentation

Connect to PostgreSQL with C++

In this article, we will introduce how to connect to a PostgreSQL cluster using C++ and some of the possible actions that you can perform on a PostgreSQL database.

The easiest way to set up a C++ client for PostgreSQL is using the libpqxx interface. First, install the libpqxx package onto your local machine. Refer to the official documentation for more information.

Connecting to a cluster

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

#include <iostream>

#include <pqxx/pqxx>

#include <string>

using namespace std;

1	using namespace std;

int main() {
try {
// Connect to the database
pqxx::connection c("user=icpostgresql password=<password> host=<comma separated node ip addresses> port=5432 dbname=postgres target_session_attrs=read-write");
} catch (const exception &e) {
cerr << e.what() << endl;
return 1;
}
}

int main() {

try {

// Connect to the database

pqxx::connection c("user=icpostgresql password=<password> host=<comma separated node ip addresses> port=5432 dbname=postgres target_session_attrs=read-write");

} catch (const exception &e) {

cerr << e.what() << endl;

return 1;

}

where <comma separated node ip addresses> is the ip addresses of your cluster nodes, and <password> is the password for the icpostgresql user. You can find this information on the Connection Info page of your cluster.

This will connect you to the postgres database on your cluster, which is created when your cluster is initially provisioned. You can then process SQL statements by creating a transaction object operating on your connection.

// Start a transaction
pqxx::work w(c);

1 2	// Start a transaction pqxx::work w(c);

You can now use the transaction’s exec, query_value, and stream functions to execute SQL statements. Make sure to call the transaction’s commit function after submitting a SQL statement to make its work final. If you don’t call this, your changes to the database will not be persistent.

Creating a table

w.exec("CREATE TABLE cities (name varchar(80), location point, population int);");

1	w.exec("CREATE TABLE cities (name varchar(80), location point, population int);");

Populating a table with rows

w.exec("INSERT INTO cities VALUES ('canberra', '(35.3, 149.1)', 395790)");

1	w.exec("INSERT INTO cities VALUES ('canberra', '(35.3, 149.1)', 395790)");

Committing the transaction

w.commit();

1	w.commit();

Querying a table

The data from a SQL query is stored in a pqxx::result object, which acts as a standard container of rows: pqxx::row.

pqxx::result rows = w.exec("SELECT * FROM cities;");
for (int i = 0; i < rows.size(); i++) {
auto [name, location, population] = rows[i].as<string, string, int>();
cout << name << ", " << location << ", " << population << endl;
}

pqxx::result rows = w.exec("SELECT * FROM cities;");

for (int i = 0; i < rows.size(); i++) {

auto [name, location, population] = rows[i].as<string, string, int>();

cout << name << ", " << location << ", " << population << endl;

}