Questions about PostgreSQL

Q: PostgreSQL documentation

PostgreSQL has excellent documentation, which can be found here (Servoy 5.2 ships with PostgreSQL 8.4)

Q: What is the equivalent of Sybase Central for PostgreSQL?

See below.

Q: What is a good Admin tool for PostgreSQL?

For PostgreSQL there are multiple admin tools available, from commercial to opensource. 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

Migrating (solutions) from database X to PostgreSQL

Q: Will all the code in my solutions still work?

All code written in Servoy is by default database agnostic. However, Servoy allows developers to interact with the database directly, through the functions of the RawSQL plugin and through databaseManager.getDataSetByQuery(). When these functions are utilized, the developer itself can choose how to write the SQL that is fired at the database and to use database specific SQL. Whether or not the SQL that works on Sybase SQL Anywhere will also work on PostgreSQL needs to be tested by the developer.
An easy way to locate all instances where the RawSQL plugin is used or the databaseManager.getDataSetByQuery is by a workspace wide search in Servoy Developer. The workspace wide search can be started by the keyboard combination Control-H.

Q: How Can I migrate my database to PostgreSQL?

This answer to this question depends on the source database, the amount of data and the existence of logic in the source database.

Servoy has a build in mechanism to generate the required datamodel when importing a solution into an (partially) empty database. The solution export/import functionality has the ability to also export and import "sample" data. When the destination database is empty, the amount of records to tranfser is not massive and the sequence type used in the solution are Servoy Sequences, this mechanism can be used to move over the data.

When the data to move is massive, a better approach would be to dump the data in a generic format and to import it again.

When the source database contains business logic (functions, stored procedures, triggers etc.) that also need migrating, more effort will be involved. How much effort depends on the source database.

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

Another option is to use the Servoy Sybase > PostgreSQL Migration Service from PgExperts: they offer services related to PostgreSQL and are also heavily involved in the PostgreSQL open source community.

Tips 'n' trick

No automatic type casting

PostgreSQL doesn't do automatic casting of types. This means that when sending in parameters into a prepared statement, the develop has 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();
		args0 = value;
		var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

Now has to become:

                var value = 1;
		var query = 'SELECT * FROM table WHERE textcolumn = ?'
		var args = new Array();
		args0 = 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'