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

How nice would be if you could design fancy-schmancy stuff in excel and plug that into your SQL process engine. Just image the possibilities, all those nasty algorithms you could come up with would be easily available to end users. Basically you would design what you wanted using a sample dataset in Excel with the same columns definitions as your real dataset. Somehow the you could drive your resultset through the excel engine and use the output. If you wanted to get fancy for larger datasets you could implement a mechanism where data could get streamed in chunks just large enough to surface the required calcs in Excel. E.g you are doing some time based calc that requires a full weeks worth of data to operate, you could then stream years worth of data in week chunks to the Excel engine. I bet this would beat the pants off of most complicated native sql based solutions as unless your well versed in sql and have a decent model within your control things start getting very slow very quickly for the average sql developer.

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
join
(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'