A small collection of useful postgresql items
The money datatype is deprecated but there is no way to convert a money value into a numeric which makes it hard to convert an old database. Here is one way to do it:
Here is a faster way to do it (that work in recent versions of postgresql).
This is an example of how one can use a procedure in PostgreSQL 8.0 (and later) to either insert a new row or update an old row if there already is one with that key.
Sometimes if you don't VACUUM often enough the tables use up more disc space then they need to. The tables are filled with dead tuples that normal VACUUM can't clean up anymore (VACUUM FULL can. but it lock tables). This query can help to identify tables with bloat by comparing the number of live rows with the number of pages needed to store them.
A good setup for postgresql is to put the WAL on a ext2 file system and the rest in a ext3 file system with the options data=writeback, noatime.
WAL files are preallocated so it should survive a crash even on ext2, at least that's what's been stated by people that should know. I would still put the WAL on a ext3 as well, with only meta data journaling just to be sure that the file system survive a crash and no files go missing due to a meta data problem.
Copyright © 2005 Dennis Björklund