Mercian Labels-a UK SME Label Printer on Open Source migration

Coming from a MsSQL database with access queries/macros/modules and so on, its easy to be able to work out weekend days from two given dates, ie if you have two dates you can calculate how many days between them aren’t weekdays.

In postgresql, this isnt so easy!

there aren’t many examples on the net, without creating mass functions, but i’ve come up with this:

SELECT count(*) FROM generate_series(1, (higherdate::date - lowerdate::date)) i WHERE date_part(’dow’, higherdate::date + i) NOT IN (0,6);

the higherdate and lowerdate are exactly what they sound like, the higherdate has to be after the lowerdate.

That will work out how many days between each of those dates, and return a result that doesnt include sat/suns basically giving you working days!

It doesnt take into account holidays, but I didnt need it to..

Quick post here, as I was having trouble finding any information(that worked, or indepth enough to work for what I needed).

The type money is now deprecated from postgresql, and a few of our tables contained this data type. This is fine, and worked as we wanted, until there came a time I needed to do a query such as:

select * from mytable where (mytable.charge > 0);

seems easy enough, but if myfieldname is of type money, you will get nice errors such as:

ERROR: operator does not exist: money > integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

Doing what the Hint suggests isnt easy, and theres pretty much no good way to cast money to anything.

A few examples on the net dont work, depending on version, or can mess up the data.

After a bit of searching and messing, ive found a solution that does whats needed:

First you need to do:

set lc_monetary=’C';

to basically control the currency, ours was set to £ but doing that sets it back to $ and makes things easier to work with.

Next you need to do:

alter table yourtable alter column yourfield type numeric(16,2) using translate(textin(cash_out(yourfield)), ‘$,’, ”)::numeric;

Once thats done, you’re set and it seems to work flawlessley (in version: 8.1.9 anyway).

May be a good idea to make a copy of the table before, just incase:

create table backup_table_yourtable AS select * from yourtable;

then if you have any issues you can revert back!