How to connect to a PostgreSQL database in C ? (libpqxx Guide)

Use libpqxx—the type-safe, exception-aware C PostgreSQL client built on libpq—by installing it (e.g., sudo apt install libpqxx-dev), linking -lpqxx -lpq, connecting via connection string, wrapping queries in pqxx::work or pqxx::nontransaction, using exec_params for safety, and converting results with .as().

Use libpqxx, the official C client library for PostgreSQL — it’s type-safe, exception-aware, and built on top of libpq. You don’t talk to the database directly; you use libpqxx to handle connections, queries, and transactions cleanly.

Install and link libpqxx

On Ubuntu/Debian: sudo apt install libpqxx-dev. On macOS with Homebrew: brew install pqxx. Then link it in your build:

• With g : g -o app app.cpp -lpqxx -lpq
• In CMake: add find_package(pqxx REQUIRED) and target_link_libraries(your_target pqxx)

Make sure PostgreSQL server is running and libpq (the C library) is available — libpqxx depends on it.

Connect with connection string or parameters

Create a pqxx::connection object. Pass a connection string like "host=localhost port=5432 dbname=test user=alice password=secret", or use named arguments:

• pqxx::connection conn{"dbname=test user=alice host=localhost"};
• Connection throws pqxx::sql_error on failure — always catch it
• Check conn.is_open() if needed, but constructor success usually means it’s ready

Run queries safely with transactions

Never run queries outside a transaction. Use pqxx::work (for read-write) or pqxx::nontransaction (for simple reads without transaction overhead):

• pqxx::work tx{conn}; auto rows = tx.exec("SELECT name FROM users WHERE id = 1");
• Call tx.commit() explicitly — or let the destructor commit if no exception occurred
• For parameterized queries (avoid SQL injection), use tx.exec_params("SELECT * FROM users WHERE age > $1", 18)

Process results with iterators or conversion

tx.exec() returns a pqxx::result. Iterate over rows and access fields by index or name:

• for (const auto &amp;row : rows) { std::string name = row["name"].as<:string>(); }</:string>
• Use .as<int>()</int>, .as<:string>()</:string>, or .as<double>()</double> — throws if conversion fails or value is NULL
• For nullable values, use row["col"].is_null() ? std::nullopt : std::make_optional(row["col"].as<int>())</int>

Basically just that — connect, wrap in transaction, query, convert. No raw pointers, no manual memory cleanup. libpqxx handles lifetimes and errors for you.

The above is the detailed content of How to connect to a PostgreSQL database in C ? (libpqxx Guide). For more information, please follow other related articles on the PHP Chinese website!