PostgreSQL C++ tutorial

Installation and configuration

This tutorial is done on LinuxMint 12 and it will work on Ubuntu 11.10. I tested it tonight on Linux Mint 13 Maya and can confirm that it still works here, that is Ubuntu Precise based Linux Mint.
Using terminal we find what is available:

apt-cache search postgresql

those are results we are interested in:

libpqxx-3.0 - C++ library to connect to PostgreSQL
libpqxx-3.0-dbg - C++ library to connect to PostgreSQL (debugging symbols)
libpqxx3-dev - C++ library to connect to PostgreSQL (development files)
libpqxx3-doc - C++ library to connect to PostgreSQL (documentation)
postgresql-9.1 - object-relational SQL database, version 9.1 server
postgresql-client - front-end programs for PostgreSQL (supported version)
postgresql-client-9.1 - front-end programs for PostgreSQL 9.1
postgresql-client-common - manager for multiple PostgreSQL client versions
postgresql-common - PostgreSQL database-cluster manager
postgresql-contrib - additional facilities for PostgreSQL (supported version)
postgresql-contrib-9.1 - additional facilities for PostgreSQL

It will return much more but we do not need them all. On Linux Mint 13 we have slightly fesher libs:

libpqxx-3.1 - C++ library to connect to PostgreSQL
libpqxx-3.1-dbg - C++ library to connect to PostgreSQL (debugging symbols)

Now in terminal we do

sudo apt-get install postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-common postgresql-common postgresql-contrib postgresql-contrib-9.1

or if one like gui, Software Manager or Synaptic will also do. Do not forget contrib packages, you will need them for pgAdmin III.
Again in terminal do:

sudo su postgres

afer entering password you are postgres. As postgres:

psql template1
psql (9.1.3)
Type "help" for help.
template1=# create role testuser login password 'testpass' superuser valid until 'infinity';
CREATE ROLE
template1=#\q

That escaped q quits psql and after one exit you are back to your login. If you like install now pgAdmin III or using psql create DB and table where you are going to practice.
To allow remote connections do:

sudo gedit /etc/postgresql/9.1/main/postgresql.conf

and modify listen_addresses, something like this:

listen_addresses = 'localhost, 192.168.0.42, 192.168.0.111'

Also in pg_hba.conf we need to enable remote users:

sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

it should look something like this, all the way towards bottom of the file:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    template1       testuser        192.168.0.0/24           md5
host    testpgdb        testuser        192.168.0.0/24           md5

There is number of commented lines in file but I ignored them to save space. After saving changes restart PostgreSQL server:

sudo /etc/init.d/postgresql restart

Please create DB testpgdb with sufficient rights for testuser or rename DB in C++ example.
Now it is time to install libpqxx. From terminal execute:

sudo apt-get install libpqxx-3.0 libpqxx-3.0-dbg libpqxx3-dev libpqxx3-doc

and installation is done. As already mentioned on newer versions of Linux it may be newer version of libpxx, so please adjust accordingly.

C++ example

Code is slightly adjusted test 005 which comes with libpqxx3-doc, to see where is what use:

dpkg -L libpqxx3-doc

It connects to local instance of PostgreSQL, if you want remote – please edit connection string. If connection succeeds creates table, inserts data and at the end does one non-transactional select.

#include <iostream> #include <pqxx/pqxx> using namespace std; using namespace pqxx; /* * */ int main(int argc, char** argv) { connection C("dbname=testpgdb user=testuser password=testpass hostaddr=127.0.0.1 port=5432"); string tableName("tabletwo"); if (C.is_open()) { cout << "We are connected to" << C.dbname() << endl; } else { cout << "We are not connected!" << endl; return 0; } work Q(C); try { Q.exec("DROP TABLE " + tableName); Q.commit(); } catch (const sql_error &) { } work T(C); T.exec("CREATE TABLE "+tableName+" (id integer NOT NULL, name character varying(32) NOT NULL, salary integer DEFAULT 0);"); tablewriter W(T, tableName); string load[][3] = { {"1","John","0"}, {"2","Jane","1"}, {"3","Rosa","2"}, {"4","Danica","3"} }; for (int i=0;i< 4; ++i) W.insert(&load[i][0], &load[i][3]); W.complete(); T.exec("ALTER TABLE ONLY "+tableName+" ADD CONSTRAINT \"PK_IDT\" PRIMARY KEY (id);"); T.commit(); nontransaction N(C); result R(N.exec("select * from "+tableName)); if (!R.empty()) { for (result::const_iterator c = R.begin(); c != R.end(); ++c) { cout << '\t' << c[0].as(string()) << '\t' << c[1].as(string()) <<'\t' << c[2].as(string()) <<endl; } } return 0; }

In order to compile code you will need to tell to g++ where are libpqxx headers (they are not on the path) and also to linker what libs must be used for linking. Something like this:

g++ hello.cxx -o hello -I/usr/local/include/ -lpqxx -lpq

If your libpqxx or libpq are on unusual place you will use -L[path to where they are], there is lot of that on CentOS or Fedora ;-)
 After executing hello (./hello) you should be rewarded with the following output:

We are connected totestpgdb
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "PK_IDT" for table "tabletwo"
        1       John    0
        2       Jane    1
        3       Rosa    2
        4       Danica  3

I can write more on subject if there is interest in it, will see from number of hits.