Postgresql information

A small collection of useful postgresql items

Cast money to numeric

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:

CREATE FUNCTION money2numeric(money) RETURNS numeric AS ' SELECT (((get_byte(cash_send($1), 0) & 255) << 24) |((get_byte(cash_send($1), 1) & 255) << 16) |((get_byte(cash_send($1), 2) & 255) << 8) |((get_byte(cash_send($1), 3) & 255)))::numeric * 0.01; ' LANGUAGE sql IMMUTABLE STRICT; CREATE CAST (money AS numeric) WITH FUNCTION money2numeric(money);

Comma aggregate

CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text, initcond='' );

Here is a faster way to do it (that work in recent versions of postgresql).

CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); which can be used like this: array_to_string(array_accum(column),',')

Insert or Update

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.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db (key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db (1, 'david'); SELECT merge_db (1, 'dennis');

Find bloat

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.

SELECT oid::regclass, reltuples::numeric, relpages FROM pg_class ORDER BY 3 DESC;

Ext3 settings

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  No Software Patents! Valid XHTML 1.1! Valid CSS!