Child pages
  • PostgreSQL Primer
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Installing PostgreSQL

Running PostgreSQL as a Service

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);

Now 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|http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL], 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.

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.

PostgreSQL documentation

PostgreSQL has excellent documentation, which can be found here. Note that Servoy 5.2.x ships with PostgreSQL 8.4.

  • No labels