This year’s t-dose meeting was fun. The very young Dutch PostgreSQL community also had a booth where we could answer various question. Personally for me it was very enlightening to see what people are considering when it comes to switching between databases. The majority of question we got was about why one should migrate from [...]

I spent the last three hours looking and Googling to solve a very annoying problem in order to compile PostgreSQL drivers natively with Qt 4.4.0 framework. After a lot of frustration I discovered that one has to provide the path to libpq.lib and the PostgreSQL include dir in UNIX style or 8.3 short filenames [...]

In this post, there is a nice explanation about executing conditional DDL. Here are my two functions which I use when I have to recreate a database schema.

create or replace function table_drop_if_exist(tbl varchar(255)) returns void as
$$
begin
if (select count(relname) from pg_class where relname=tbl) 0 then
raise notice ‘dropping [%] with cascade’,tbl;
execute ‘drop table ‘ || [...]

Using sequences are common practice in order to have auto numbering in a PostgreSQL database. The easiest way to create an auto numbered identity field is to use the SERIAL data type macro.

Here is an example:

CREATE TABLE customer
(
customer_id SERIAL NOT NULL PRIMARY KEY,
name varchar(50)
);

When running this script, PostgreSQL automatically will create a [...]

One of the cool new features in PostgreSQL in the connect privilege. The CONNECT privilege enables you to grant connection rights from a user to a database, avoiding the need to configure user to database access in the pg_hba.conf.
The information in the pg_hba.conf could exclusively be used to administer the authentication (Are you allowed on [...]

This maybe trivial but I think it is worth sharing anyway
Consider using LIMIT/OFFSET in a function where as parameter you have to specify values for both.
In the following function both limit and offset parameters are integers. This will cause an error when you want to pass ALL (for LIMIT ALL) as argument.
PostgreSQL function:

create or replace [...]