Difference between revisions of "postgres"

From Lazarus wiki
Jump to navigationJump to search
(→‎Overview: win64 warning)
Line 6: Line 6:
* It is very widely used and available
* It is very widely used and available
* Very stable and has a complete feature set
* Very stable and has a complete feature set
* Liberal license (no costs) in comparison with MySQL
Disadvantage of PostgreSQL:
Disadvantage of PostgreSQL:
* Some hosters may not offer PostgreSQL)
* Some hosters may not offer PostgreSQL)
* No embedded version
* No embedded version
Win64: please see warning [[Windows Programming Tips#FPC 2.6.x/Lazarus warning|here]] on not using certain FPC/Lazarus Win64 versions.

Revision as of 10:31, 1 March 2014


You can use Free Pascal/Lazarus to access a PostgreSQL database server. If you are looking for information on the postgres package in FPC, please see postgres#PostgreSQL_package:_the_low_level_units below.

Advantages of PostgreSQL:

  • It is very widely used and available
  • Very stable and has a complete feature set
  • Liberal license (no costs) in comparison with MySQL

Disadvantage of PostgreSQL:

  • Some hosters may not offer PostgreSQL)
  • No embedded version

Win64: please see warning here on not using certain FPC/Lazarus Win64 versions.


Zeos supports PostgreSQL; please see ZeosDBO


FPC/Lazarus supports PostgreSQL out of the box with a PostgreSQL connection component/class. If you are using FPC only or want to manually add PostgreSQL support, add pqconnection to your uses clause.

Note: The libpq C client contains some memory leaks (at least up till version 9.3 of Postgres) when a library is repeatedly loaded/unloaded. SQLDB loads the library when the first connection is made, and unloads it when the last connection closes. This means that whenever the last connection is closed, a small memory leak is created. To prevent this from happening (and speed up the application), you can load the library once at the start of the process with the InitialisePostgres3 call.

The charset property is used for client encoding.

The TPQConnection component does not directly support a Port property, but one can pass the port into the component via the Params parameter:

PQConnection.Params.Add('port=' + VariableContainingPort);

Other PostgreSQL specific connection parameters can be specified using the Params property:



See SQLdb_Tutorial1 for a tutorial on creating a GUI database-enabled program that is written for PostgreSQL/SQLDB, as well as SQLite/SQLDB, Firebird/SQLDB, basically any RDBMS SQLDB supports).

Event monitoring

If you have FPC2.6.2+ and a recent version of Lazarus (note: currently, Oct 2013, Lazarus is set to require FPC2.6.3+ for this to work), you can use the TPQTEventMonitor component to monitor events coming from PostgreSQL.

It is a thin wrapper around FPC PQEventMonitor; please see the FPC pqeventstest.pp example programs for details.

Installation and errors

As with all sqldb units, you need to add your driver libraries (all required PostgreSQL .dll/.manifest files)

  • to a directory in the (library search) path (e.g. c:\windows\system32 for Windows)
  • or (Windows) to the program output directory (e.g. lib/something/ in your project directory, or perhaps the project directory depending on your project options)

Windows 64 bit driver

If you are developing 64 bit applications, you must use a 64 bit DLL.

Light bulb  Note: FPC 2.6.0 (therefore Lazarus 1.0.4 or lower) does not yet support PostgreSQL on Windows 64 bit (unless you patch and recompile it).

A Windows 64 driver is fairly hard to find but can be downloaded here: [1]. The driver library can be installed in c:\windows\system32; 32 bit driver libraries can be installed in the confusingly named c:\windows\syswow64

Error: "Can not load PostgreSQL client library "libpq.dll""

The program cannot find your PostgreSQL driver files.

See above on instructions where to install the libraries.

A good example that demonstrates how to include drive DLL files when connecting Lazarus with PostgreSQL under Windows is easyDB.

On Linux/Unix/OSX: make sure the PostgreSQL libraries are in your library search path, e.g.:

  • On Linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
  • It may be necessary to create a symbolic link from a specific library version to a general library name:
    ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
    . Alternatively, install the postgresql client -dev package using your distribution's package manager

PostgreSQL package: the low level units

As with all databases, the SQLDB code depends on a lower level PostgreSQL specific unit that wraps around the PostgreSQL driver library (.so/.dll/.dylib). Normally, you would use the higher-level SQLDB code as it allows you to code more quickly, easily switch databases etc.

Using this is very easy, all you need to do is compile some units, and use these units in your program. You need to specify the place of the PostgreSQL client Library (libpq) when compiling, and that is it.

The main unit is called postgres, normally this is the only unit you must include in your uses clause.


You need at least version 0.99.5 of Free Pascal (basically any version of FPC except extremely old ones). The headers are translated from PostgreSQL version 6.3.1.


The postgres unit comes with the Free Pascal packages, and is distributed together with the compiler. This contains a directory postgres with the units, a test program and a makefile. cd to the directory and edit the Makefile to set the variables for your system. You must provide only 1 thing:

  1. The directory where the libpq library resides, usually /usr/local/pgsql/lib



Should compile the units and the program. If compilation was succesfull, you can install with

make install

(Remember to set the directory where the units should be installed.)

You can then test the program by running

make test

This will:

  • Run the test program testpg. It is a straightforward pascal translation of the example program in the PostGreSQL programmers' guide.
  • Run a script to create a table in a database, and fill it with some data. (the psql program should be in your PATH for this) . By default, the used database is testdb.
  • Run the testprogram testemail
  • Run a shell script again to remove the created table.

You will see a lot of messages on your screen, giving you feedback and results. If something went wrong, make will inform you of this.

Go back to Packages List

See also