Category: PostgreSQL

Easy PostgreSQL docs for Sublime Text

I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:

"docs": {
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
"sql": ""

"pascal": "",
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
"fallback_scope": "google"

ST3 context help  for pg sql
ST3 context help for pg sql

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)

left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

  2. MERGE INTO acme_obj_value d
  3. USING ( SELECT object_id
  4.         FROM   acme_state_tmp
  5.       ) s
  6. ON (d.object_id = s.object_id)
  7.   WHEN matched THEN
  8.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  9.   WHEN NOT matched THEN
  10.     INSERT (, d.object_id, d.date_value)
  11.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

  2. WITH s AS (
  3.      SELECT object_id
  4.      FROM   acme_state_tmp
  5. ),
  6. upd AS (
  7.      UPDATE acme_obj_value
  8.      SET    date_value = LEAST(l_dt, d.date_value)
  9.      FROM   s
  10.      WHERE  acme_obj_value.object_id = s.object_id
  11.      RETURNING acme_obj_value.object_id
  12. )
  13. INSERT INTO acme_obj_value(id, object_id,  date_value)
  14. SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
  15. FROM   s
  16. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

Get PostgreSQL sources using SVN

As you probably know PostgreSQL sources are managed by Git version control system. Which is great, but for me as SVN user it would be much better to get access to the sources using familiar tools, e.g. TortoiseSVN. Mainly because I don’t need write access, read mode only. There is a workaround for such guys.

First of all, there is a mirror of PostgreSQL sources on the GitHub. And the second, GitHub supports SVN protocol using the bridge to communicate svn commands to GitHub.

So in my case I need only trunk with the latest commits. I’ve created an empty folder and made Checkout to the URL.Image

Dump and restore of PostgreSQL: version compatibility FAQ

Compress PostgreSQLMany newbies are confused about how PostgreSQL dump and restore work. And even more questions appeared about version mismatches of the server itself and tools used. After fresh PostgresDAC release we’ve faced with the same questions.

Utilities used for dump and restore are called pg_dump and pg_restore respectively. Delphi developers may use TPSQLDump and TPSQLRestore components which are fully compatible with native tools.

So here I’m trying to create some kind of a FAQ.

Let’s assume version is a concatenation of Major, Minor and Build numbers separated by dots, e.g. 9.3.2

Q: Can I downgrade the database version, e.g. from 9.3 to 8.4?
A: If you mean official supported way of doing this, then no, you can’t. You may however use pg_dump in plain text output mode and manually fix all incompatibilities.

Q: Can I create dump of old server with new pg_dump?
A: Yes, sure. All versions since 7.0 are supported now. But if you use custom format, be ready the same or higher version of pg_restore will be needed.

Q: Can I create dump of new server with old pg_dump?
A: pg_dump must have the same major and minor version or higher, e.g. pg_dump v9.2.3 will work on v9.2.5, 9.2.x, 9.1.x, 8.x etc. and will not work for versions like 9.3.x, 9.4.x etc.

Q: What if I want to restore custom dump archive to the old server anyway?
A: You should use pg_restore to generate plain SQL script, manually edit it to fix version mismatches.

Q: Will pg_restore “understand” archives of newer versions of pg_dump?
A: No. The rule is simple: pg_restore will work with pg_dump output of the same major and minor version or lower, e.g. pg_restore v9.3.x will understand pg_dump output v9.3.x, 9.2.x, 9.1.x, 8.x etc. and will not work with output from versions like 9.4.x.

Q: Can I use new output format of the pg_dump with old server version, e.g. directory output for 8.4 server?
A: Yes, you can.

Q: Can I use new parallel functionality with old servers?
A: Yes, you can, but there is a limitation for pre-9.2 servers. See description for –no-synchronized-snapshots parameter.

PS All these statements are correct for the PostgresDAC’s TPSQLDump and TPSQLRestore components.

Development using Lazarus and PostgresDAC. Installation


There are a lot of tools for PostgreSQL development. I want to propose you one more. Why Lazarus:

  • Lazarus is shipped with open-source Pascal compiler;
  • it has a high degree of Delphi compatibility;
  • availability on a variety of platforms, including Windows, Mac OS X, and Linux;
  • complete source code is available;

Installing Lazarus

Go to Lazarus at SourceForge.

Then at the Downloads Section get proper installer. I used lazarus-1.0.8-fpc-2.6.2-win32.exe and lazarus-1.0.8-fpc-2.6.2-win64.exe in my tests. These are the latest releases at the moment.

Here are the screen shots of the installation process where you can see what I had chosen:
Lazarus Installation

Path to install

Full installation

I have Lazarus installed into C:\lazarus\ and hereafter I will use this path.

Getting PostgresDAC

Now it’s time for PostgresDAC. At the moment many Laarus IDE functions require the source and warns if the source of a unit is missing. Thus we need PostgresDAC with sources version. Sources are available for licensed users in their profile.

MicroOLAP Profile

I have extracted PostgresDAC sources to C:\PostgresDAC\.

Install PostgresDAC into the IDE

In the Lazarus IDE choose open package and select dclPostgresDACL.lpk. This is the design time package. Then in the Package window choose Use → Install.
Install package

Confirmation about rebuild will appear.


Choose “Yes”. Wait for some time. After successful rebuild Lazarus will reopen itself. Tada! We got it!

PostgresDAC in the Lazarus

Now we may open some demo projects and play some time.
GridDemo for PostgresDAC in the Lazarus