Saturday, March 29. 2008

You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. How do you rename this database?

Solution:

If you are using PgAdmin III, you will not see this option. Just one of the ways PgAdmin III lets us down. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error.

Connect to some other database other than the one you are trying to rename such as say the postgres db.

Kick everyone out of the database you are trying to rename - to figure out users, you can run

Friday, March 28. 2008

What is Constraint Exclusion?

Constraint Exclusion is a feature introduced in PostgreSQL 8.1 which is used in conjunction with Table Inheritance to implement
table partitioning strategies. The basic idea is you put check constraints on tables to limit what kind of data can be inserted into it.
Constraint Exclusion will then in theory skip over tables whose check constraints guarantee there is no way for it to satisfy the
condition of a query.

Constraint Exclusion is a great thing, but it has some limitations and quirks one needs to be aware of that in some cases
will prevent it from kicking in. Below are a couple of reasons why it sometimes doesn't work.

Monday, March 24. 2008

What is PuTTY

PuTTY was developed by Simon Tatham and is a very common light-weight MIT-Licensed
free and open source Secure Shell (SSH) client for connecting to Linux/Unix systems via a Teletype (TTY) terminal emulation mode console.
Currently there are ports for Microsoft Windows, other unix like systems,
and ports in progress for Mac OSX and Symbian mobile phone OS.

PuTTY fits into that class of tools we affectionately call Swiss Army Knives because it is
Light, Multi-Purpose, and Good Enough. As an added benefit it is free and open source with a generous license so it is commonly embedded in
commercial apps.

In this article we shall cover how to use PuTTY's SSH Tunneling feature to access a remote PostgreSQL server that doesn't allow
remote connections. To make it a little more interesting we shall demonstrate how to do this for PgAdmin III.