Detailed Description

PostgreSQL is an open-source relational database known for its robust SQL compliance and extensibility.

Connecting

To connect to a PostgreSQL database, use the PostgresDB factory:

#include <PostgresDB/PostgresDBFactory.h>
...
Database db(PostgresDB::PostgresDBFactory().Create("host=localhost port=5432 user=foo password=bar"),
            "database-name");

The PostgresDBFactory's Create() method accepts a connection string described in PostgreSQL documentation, e.g. "host=localhost port=5432 user=foo password=bar".

The second argument to the CDP2SQL::Database constructor is the database name. If the database does not exist, it will be created automatically. The database name can be left empty if the connection string already contains the database name, e.g. "host=localhost port=5432 dbname=mydb user=foo password=bar" but in that case, the database will not be created automatically.

URI Format

There is partial support for the URI connection format described in PostgreSQL documentation, e.g. "postgresql://foo:bar@localhost:5432/mydb".

In that case, the database name must be included in the URI and automatic database creation is not supported (leave the second argument of CDP2SQL::Database constructor empty).

Environment Variables

The connection parameters can also be specified using environment variables as described in PostgreSQL documentation. This can be useful to avoid putting passwords in application code. Here is an example of some commonly used environment variables:

PGHOST=127.0.0.1
PGPORT=5432
PGUSER=foo
PGPASSWORD=bar
PGDATABASE=mydb

It is allowed to mix connection string parameters with environment variables, e.g. to only specify the password using PGPASSWORD environment variable.

If the database name is specified using PGDATABASE environment variable, leave the second argument of the CDP2SQL::Database constructor empty. Automatic database creation is not supported in that case.

Parameter Binding

While PostgreSQL natively supports named parameters using the $1, $2... syntax, the CDP2SQL interface also supports the more common ? placeholder for parameter binding. This allows easier portability of code between different database systems, but it means a literal ? that is not between quotes (e.g. part of a string literal) must be escaped as ?? in SQL statements. For example, when using the ? operator in JSONB queries, write it as ??.

This behavior can be disabled by calling the SetNativeParameterBindingEnabled() method on the connection object before creating any CDP2SQL::Statement objects.

auto connection = PostgresDB::PostgresDBFactory().Create("host=localhost port=5432 user=foo password=bar");
connection->SetNativeParameterBindingEnabled(true);
Database db(connection, "database-name");
...

See Also

• PostgresDB::IPostgresDBConnection