Connect to PostgreSQL (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to PostgreSQL.

Supported Versions

The FireDAC native driver supports PostgreSQL Server and PostgreSQL Advanced Server version 7.4 and later, because it requires a PG protocol 3.0 connection.

Windows Client Software

FireDAC requires the LIBPQ.DLL x86 or x64 client library for connecting to the PostgreSQL server. Using libpq.dll also requires the "Microsoft Visual C++ 2010 Redistributable Package" to be installed. You can get this package from . Ideally, the libpq.dll version should be equal to the server version. The full set of the v 9.0 client files:

  • libpq.dll
  • ssleay32.dll
  • libeay32.dll
  • libintl-8.dll
  • libiconv-2.dll

You can take them from the server (details) installation Bin folder and place them in:

  • a folder listed in your PATH environment variable.
  • your application EXE folder.
  • any other folder and specify in FDDrivers.ini:

If the PostgreSQL client library has not been installed properly, an exception is raised when you try to connect:

[FireDAC][Phys][PGSQL]-314. Cannot load vendor library [LIBPQ.DLL]. The specified module could not be found.
Check [LIBPQ.DLL], which is located in one of the PATH directories or in application EXE directory.


The ordinal Nnn could not be located in the dynamic link library SSLEAY32.dll.

Linux Client Software

FireDAC requires:

  • the x86 or x64 client library.

To install on Linux use the commands:

sudo apt-get update
sudo apt-get install libpq-dev

Mac OS X Client Software

FireDAC requires:

  • the libpq.dylib x86 client library.

It comes pre-installed on Mac OS X or can be installed separately (more).

iOS Client Software

The article (more) explains how to build libpq.dylib for iOS.

Note, Embarcadero Technologies has not tested this and does not provide assistance with it.

Driver Linkage

To link the driver:

Additional Setup

If an application is using the {TIMESTAMPDIFF(MONTH, ....)} escape function, then we recommend that you create 3 PostgreSQL functions with the following types of arguments:

  • DATE

The function template:

CREATE OR REPLACE FUNCTION MONTHS_BETWEEN (timestamp with time zone, timestamp with time zone) RETURNS integer AS
  mes INTEGER;
  mes1 INTEGER;
  ano INTEGER;
  mes=extract(month from (age($1,$2)));
  ano=extract(year from (age($1,$2)));
  mes1=abs((ano*12) + mes);
  return mes1;

Connection Definition Parameters

To connect to a PostgreSQL DBMS, most applications require you to specify DriverID, Server, Database, User_Name, Password, CharacterSet.


Parameter Description Example value
Server The TCP/IP address or host name of the server running a PostgreSQL server.
Port The TCP/IP port on which PostgreSQL server is listening. 5432
Database Name of the current database for the connection. If the Database is not specified, no current database is set up. MyDB
User_Name The PostgreSQL user ID. postgres
Password The PostgreSQL user password.
CharacterSet The default character set for the connection. For details, see the Character Set Support chapter. WIN1251
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 30

Controls the extended description of the query result sets:

  • True - FireDAC describes a result set to get all the possible column attributes - is nullable, is auto incrementing, to which domain it belongs, etc. Setting this option to True, slightly slows down a dataset opening.
  • False - FireDAC uses the restricted information about the query columns (default).

Controls the interpretation of an OID column in a table:

  • No -- an OID column is a dtUInt32 column (contains unsigned integer values).
  • Yes -- an OID column is a dtHBlob column (contains Large Object values).
  • Choose -- if a query selects data from the dictionary tables or a column that is not of a LO, LargeObject or BLOB domain, then an OID column is a dtUInt32 one, otherwise - a dtHBlob one. The ExtendedMetadata option must be True to get a column domain (default).

Controls the handling of an unknown PostgreSQL data type:

  • Error -- raise the exception "Cannot describe type" (default).
  • BYTEA -- represent as a BLOB value.
ApplicationName Name of the application. If specified, this value is stored in the pg_stat_activity table, application_name column. AllBooks
PGAdvanced Additional PostgreSQL server connection options. For details, see the Database Connection Control Functions chapter, PQconnectdb paragraph. A value format is - <option>=<value>[;...].
MetaDefSchema Default schema name. The design time code excludes the catalog name from the object name if it is equal to MetaDefSchema. Setting MetaDefSchema does not change the current schema search path in the PostgreSQL session. 'public' is the default value. MySchema

Usage Cases

  • Connect to a server running locally, listening on the default (5432) port:
  • Connect to a remote server, listening on a non-default port, using Unicode for character data:

See Also