PostgreSQL Primer

This tutorial is by no means a replacement for the excellent PostgreSQL documentation that can be found here. The goal of this tutorial is to provide a starting point for the most common actions for working with PostgreSQL, with pointers to more in-depth documentation where applicable.

Installing PostgreSQL

While PostgreSQL comes bundles with Servoy, the PostgreSQL installation provided through the Servoy All-In-One installer is a basic installation. Thus many people prefer

Unix variants

Windows

As of Servoy 5.2 PostgreSQL is bundled with Servoy by default. The bundles PostgreSQL is however a very basic install. For production purposes it is also possible to use a dedicated PostgreSQL installer from EnterpriseDB.

Upgrading PostgreSQL

Starting and stopping PostgreSQL manually

Running PostgreSQL as a Service

Performing backups

Manual backups

Scheduled backups

PostgreSQL Database Admin tool

For PostgreSQL there are multiple admin tools available, from commercial to open source. The most used admin tool for PostgreSQL is the open source, cross-platform PgAdmin. It can be downloaded here and the excellent documentation is available here.

PostgreSQL SQL Tips 'n' Tricks

No automatic type casting

PostgreSQL doesn't do automatic casting of types. This means that when sending in parameters into a prepared statement, it is up to the developer to make sure to send in the right type.

For example:

var value = 1;
var query = 'SELECT * FROM table WHERE textcolumn = ?'
var args = new Array();
args[0] = value;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

Has to become:

var value = 1;
var query = 'SELECT * FROM table WHERE textcolumn = ?'
var args = new Array();
args[0] = value + ""; //forcing the integer value to become a string
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

Quoting strings and aliases

In PostgreSQL, the use of quotes and double quotes is more strict than in some other databases.

Quoting for column Aliasses:

SELECT some_column as "A nice name" FROM table

Quoting literal strings:

SELECT * FROM table where text_column = 'someValue'

Migrating to PostgreSQL

A good starting point to data migration towards PostgreSQL is the PostgreSQL wiki, which has a lot of tutorials migrating data into postgreSQL from many different source databases.

When migration an existing solution developed in Servoy from one type of database to PostgreSQL, Servoy's built-in mechanism to generate the required data model in a new database and to move sample data can also be used to migrate to PostgreSQL (or any other type of database). There are however some restrictions. Such a migration will not move views, stored procedures and functions, database level triggers and sequences. If the amount of records to be moved is very large, memory issues can occur due to the sheer amount of data. To make sure that PK-FK integrity stays in tact in import of the sample data, either Servoy Sequences must be used or the database level generation of sequence values (especially for PK sequences) must be temporarily disabled.

When the amount of data to be moved to the new database is massive or required pre-processing, a better solution is to dump the data to a generic format and import it directly into the new database.

When migrating from Sybase to PostgreSQL, PgExperts provides a special migration service for Servoy customers. For more information on this service check the Pgexperts website.

Child pages