PostgreSQL Backend - "postgresql"

PostgreSQL backend allows to connect to PostgreSQL database. It uses libpq C client API.

Connection String

The driver name is "postgresql", cppdb::session::engine() returns "posgresql"

PostgreSQL connection properties are passed as it to libpq. So for full list you should refer to the libpq manual

The most used properties are:

• host - the remote database host to connect. Default is local host.
• user - the user name to login
• password - the password to login
• dbname - the name of the database to use - default unspecified
• port - the port to connect - default unspecified

Special Properties

PostgreSQL backend has additional internal property that define how to treat blob objects: "@blob"

The possible values are:

• lo use large object API to store Blobs. This is the default.it adds a restriction to accessing large objects only withing transaction and handing their lifetime using lo module. This option has an advantage of small memory footprint when dealing with large objects as it does not require storing full object in memory.
• bytea - treat Blobs as bytea columns. This is simpler method but it is applicable only for objects that can fit to memory.

Implementation Details

Prepared statements are implemented using PQexecPrepared API, while unprepared statements use PQexecParams API.

When using PostgreSQL large objects "@blob=lo" - the default - you should access them only during transaction, otherwise the operations would fail. It is very good idea to use lo module that helps handing object lifetime as cppdb backend is not aware of statement type you use and it can't decide whether new object should be created in insert statement or same object should be updated. So "lo" module is your friend.

You may also use bytea if want to have a semantics similar to other RDBMSs Blobs.

Fetching last insert id should be done using non-empty sequence name, i.e. using cppdb::statement::sequence_last() and it is fetched using "SELECT currval(?)" statement.