Connect to PostgreSQL (FireDAC)
Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to PostgreSQL.
Contents
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.
Client Software
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" installed. You can get this package from http://www.microsoft.com/en-us/download/details.aspx?id=8328. 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
Alternatively you may put the required files in any other folder, and specify their path in FDDrivers.ini:
[PG]
VendorLib=<folder>\libpq.dll
If the PostgreSQL client library has not been installed properly, an exception is raised when you try to connect:
or
OS X Client Software
FireDAC requires:
- the libpq.dylib x86 client library.
It comes pre-installed on 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.
Linux Client Software
To install the PostgreSQL client library:
-
On Ubuntu Server 16.04 LTS, run:
sudo apt-get install libpq5 sudo ln -s /usr/lib/x86_64-linux-gnu/libpq.so.5 /usr/lib/x86_64-linux-gnu/libpq.so
-
On Red Hat Enterprise Linux 7, run:
sudo yum install postgresql-libs sudo ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so
Driver Linkage
To link the driver:
- drop a TFDPhysPgDriverLink component from the "FireDAC Links" palette page
- or include the FireDAC.Phys.PG unit in a uses clause.
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
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
The function template:
CREATE OR REPLACE FUNCTION MONTHS_BETWEEN (timestamp with time zone, timestamp with time zone) RETURNS integer AS
$body$
DECLARE
mes INTEGER;
mes1 INTEGER;
ano INTEGER;
begin
mes=extract(month from (age($1,$2)));
ano=extract(year from (age($1,$2)));
mes1=abs((ano*12) + mes);
return mes1;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Connection Definition Parameters
To connect to a PostgreSQL DBMS, most applications require that you specify DriverID, Server, Database, User_Name, Password, and CharacterSet (see Defining Connection (FireDAC) for details).
DriverID=PG
Parameter | Description | Example value |
---|---|---|
Server | The TCP/IP address or host name of the server running a PostgreSQL server. | 127.0.0.1 |
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 |
ExtendedMetadata |
Controls the extended description of the query result sets:
|
False |
OidAsBlob |
Controls the interpretation of an OID column in a table:
|
Yes |
UnknownFormat |
Controls the handling of an unknown PostgreSQL data type:
|
BYTEA |
ArrayScanSample | Determines whether the constrained arrays are mapped to ftArray or ftDataSet .
To specify this connection parameter use ArrayScanSample=<RowsToScan>[;<DefaultArraySize>]. It performs as follows:
|
|
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 |
GUIDEndian | It defines how the GUID value is represented on the client. 'Little' is the default value. | Big |
Use Cases
- Connect to a server running locally, listening on the default (5432) port:
DriverID=PG Database=addemo
- Connect to a remote server, listening on a non-default port, using Unicode for character data:
DriverID=PG Server=pgsrv Port=5433 Database=addemo CharacterSet=utf8 MetaDefSchema=MySchema ExtendedMetadata=True