PostgreSQL

Requirements

  • Build: libpq development files (libpq-dev), found with pkg-config.
  • Run: libpq.

Connection string

pgsql://[user.password@]host[:port]/database[?options]

Example:

pgsql://localhost:5432/demo?default_schema=app&autocommit=off

The user name and password are usually given in the CONNECT statement. The libpq environment variables (PGHOST, PGPORT, PGPASSWORD, PGSSLMODE…) and the ~/.pgpass file are taken into account by libpq for what the connection string does not give.

Options

default_schema
the schemas of the search_path, separated by commas.
client_encoding
the client encoding, for example UTF8 or LATIN1.
native_cursors
on (default) to use PostgreSQL cursors (DECLARE ... CURSOR, FETCH); off to execute the query when the cursor is opened and keep all the rows in memory.
decode_binary
on (default) to decode bytea values; off to receive them in their text encoding (larger).
libpq options
hostaddr, connect_timeout, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, sslmode, requiressl, sslcert, sslkey, sslrootcert, sslcrl, krbsrvname, gsslib, service are passed to libpq. See the libpq documentation.

Cursors and transactions

Native cursors only exist inside a transaction. With the default autocommit mode, every statement is its own transaction, and OPEN fails. Either:

  • start a transaction before opening the cursor (EXEC SQL START TRANSACTION END-EXEC), or
  • connect with autocommit=off (or GIXSQL_AUTOCOMMIT=off), or
  • use emulated cursors (native_cursors=off).

Running Oracle programs

See Migrating from Pro*COBOL for the orafce extension and the search path.

Testing

NoteTo be completed

How to set up a test database (Docker image, orafce).