PostgreSQL: Installing on windows using alternate service username

I came across this scenario the other day. I had to install PostgreSQL 9.0 on a windows box that already had an old PostgreSQL install and hence a local postgres account already setup. Using the same postgres account was not an option as a) I didn’t know the password and b) if the the password ever changed nobody would update the PostgreSQL 9.0 service entry with the updated password.

Fortunately there is an easy way to do this. Launch the one-click installer from the command line with the following arguments

postgresql-9.0.01-1-windows.exe --serviceaccount username

This will allow you to complete the installation as usual but the service account username will be the value passed. You can confirm this on the screen which asks you for the password. You can also complete the whole installation unattended just Launch the one-click installer with –help argument for a full list of options.

Pluggable Excel processing engine


PostgreSQL: The joy of generate_series

Using generate_series to build up data as required for reporting using a temporal modeled table.

create table temporal_table (
some_value integer,
date_from timestamp default current_date,
date_to timestamp default '3000/12/31 23:59:59'::timestamp

insert into temporal_table values (1, '2010/01/01', '2010/01/05 23:59:59');
insert into temporal_table values (2, '2010/01/06', '2010/01/08 23:59:59');
insert into temporal_table values (3, '2010/01/09', '2010/01/31 23:59:59');
insert into temporal_table values (4, '2010/02/01');

select *
from temporal_table
(select generate_series calendar_date
from generate_series('2010/01/01 00:00'::timestamp, '2010/01/31 23:59:59', '1 day')
) as period
on  period.calendar_date between temporal_table.date_from and temporal_table.date_to
where date_from between '2010/01/01' and '2010/01/31 23:59:59'