PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

11-Aug-2003 Issue: 38

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

7.4 beta is now available
[ANNOUNCE] v7.4 Beta 1 Bundle Available for Testing 5-Aug-2003

The first build of 7.4 for beta is now available. Go to the PostgreSQL Mirrors to choose a mirror and then select beta. Alternatively, you can go to the developers beta site The CVS tag is REL7_4_BETA1. Both .gz and .bz2 file formats are available.

Alternatively, you can pick up CVS HEAD and stay up to the minute with the bug fixes being applied to this beta release. You can watch or review the changes with the CVS Web interface. You can also download individual files from the CVS WEB.

Beta releases, as you know, are not production quality releases. (Well, they might be, but don't make that assumption.)

It is extremely critical for as many of us as possible to download and test 7.4. This is our primary opportunity to have wide community feedback about the release. Test and Speak now! The bugs, if any, that you find will be given high priority. And all of this will lead to an even more solid productuction release for 7.4.

Bugs found in the beta release should be reported in pgsql-bugs or pgsql-hackers. And they are coming in as we speak, ahem, write, read, whatever... The early bugs are seem to be mostly platform specific. The following warning messages can be ignored for now. It is flex complaining.

	 In file included from preproc.y:6278:
	 pgc.c: In function `yylex':
	 pgc.c:1386: warning: label `find_rule' defined but not used
	 /usr/include/ctype.h: At top level:
	 pgc.c:3367: warning: `yy_flex_realloc' defined but not used

Note: Now is also the time to be thinking about your upgrade path. Plan for it and schedule time to upgrade to 7.4 when it is released for production.

Contributors: Marc Fournier at elein at
Feature Overview of PL/R
[ANNOUNCE] GBORG NEWS: PL/R Version 0.5.0-alpha 5-Aug-2003

PL/R is the R Procedural Language for PostgreSQL. It enables user-defined SQL functions to be written in the R language.

R is `GNU S' - A language and environment for statistical computing and graphics. R is similar to the award-winning S system, which was developed at Bell Laboratories by John Chambers, et al. It provides a wide variety of statistical and graphical techniques (linear and nonlinear modelling, statistical tests, time series analysis, classification, clustering, etc.). PL/R allows access to R's analytical power efficiently and consistently, while enabling leverage of scarce specialty skills and processing hardware.

PL/R is one of the most advanced Procedural Languages available for PostgreSQL. It supports most, if not all, of the relevant new features implemented in PostgreSQL 7.3 and 7.4. Specifically:

  • Table Function support (can return rows and columns).
  • Converts Postgres array arguments to R vectors, matrixes, or 3D arrays as appropriate, and vice-versa on return.
  • Allows polymorphic function definition (arguments and return type may be anyarray or anyelement at function creation time allowing the same function to disparate or similar types. accept, e.g. INT8 on one call, and TEXT on the next).
  • Compiled function cache specifically supports polymorphic functions.
  • Preinitialization support (preload_libraries)
  • "firstpass" variable support (allow global variables to be reset on the first call to the function within a SQL statement).
  • Nested error contexts.
With the recent addition of trigger support, and given the power and completeness of the R programming language, PL/R can fill virtually any need you have for a Procedural Language with PostgreSQL. And for analytical or array manipulation related tasks, it can't be beat.

You must have downloaded and installed R in order to use PL/R. PL/R can be downloaded and reviewed from GBORG Documentation for PL/R is available at Contributors: Joe Conway mail at
Fun with Aging
[GENERAL] birthday calculation 22-Jul-2003

Given a timestamp the age() function will return an interval which is the difference between now and the timestamp. So how old are you really?

	elein=# SELECT age('12/31/59'::timestamp);
	 -56 years -4 mons -23 days
Ooops. Y2K hits again. Don't forget to use four digit years.

Let's try again. By adding the extract function, you can select out parts of the interval, years, months, days, etc. This does not round the interval value or convert the value into days, or hours. It simply pulls out the piece requested.

	elein=# SELECT age('12/31/1959'::timestamp);
	 43 years 7 mons 8 days
	elein=# SELECT extract(year FROM age('12/31/1959'::timestamp));
	elein=# SELECT extract(days FROM age('12/31/1959'::timestamp));

A variation that is sometimes useful is to use date_trunc. Instead of selecting out the named pieces, it truncates the date to the level requested.

	=# SELECT date_trunc('days' , age('12/31/1959'::timestamp));
	 43 years 7 mons 8 days
	=# SELECT date_trunc('months' ,age('12/31/1959'::timestamp));
	 43 years 7 mons
	=# SELECT date_trunc('years' , age('12/31/1959'::timestamp));
	 43 years

Contributors: Ben bench at, nolan at, Dennis Gearon gearond at, Network Administrator netadmin at, Greg Sabino Mullane greg at
Storing an encrypted password
[GENERAL] How to encrypt data in Postgresql 24-Jul-2003

contrib/pgcrypto enables storing of encrypted data in fields. With the functions it provides you can encrypt and store the data as well as verify passwords.

	create table users (
	   name  text,
	   passwd   text
	insert into users (name, passwd) values ('harold', crypt('1234', gen_salt('md5')));
	select * from users;
	=# select * from users;
	  name  |               passwd
	 harold | $1$Ahuzwzzz$gdWC5OZLMk8Hhqa1V7Lbz/
	(1 row)
	=# select '$1$Ahuzwzzz$gdWC5OZLMk8Hhqa1V7Lbz/' = crypt('1234', passwd) 
		from users where name='harold';
	(1 row)
	=# select name from users where passwd = crypt('1234', passwd);
	(1 row)

Note that when using encryption, the convention is to store and compare the encrypted value. Being able to decrypt stored passwords is generally considered to be a "bad thing".

To install contrib/pgcrypto from source, in that directory, perform make, make install. To install the functions into a database, use

	psql mydb < pgcrypto.sql 
If you need encryption in all of your databases, install pgcrypto into template1 and subsequently created databases will have the functions predefined.

See the documentation in contrib/pgcrpto for more information on the full capability of pgcrypto.

Contributors: Terence Chang TChang at, Richard Welty rwelty at, Franco Bruno Borghesi franco at, Douglas Trainor trainor at, Reuben D. Budiardja techlist at, Benjamin Jury benjamin.jury at
Use Triggers, not Rules for History Logging

One technique for tracking updates of a table is to store the old values into a another table as a log of changes. All or most of the columns of the original table are duplicated with in the log table and a timestamp or other key is added.

This technique is best done with a trigger instead of an UPDATE RULE, particularly if the UPDATE RULE was to apply to a view. A possible update rule might be this:

	CREATE RULE update_org AS ON UPDATE TO orig_table_view DO INSTEAD
		UPDATE orig_table set col=NEW.col...
		INSERT INTO log_table ...( old.col... )
This rule will not work because when the original table is updated, the OLD values have changed and are visible. Therefore the insert into the log table logs the new values rather than the old ones. If you switch the order of the UPDATE and INSERT statements, this RULE should work for logging.

However, with TRIGGERS you do not need to wrestle with the visibility rules. You will still need the rule for updating the view. The trigger will be on the base table and insert the log record only.

To see both techniques in action check out Logging Updates.

Contributors: Justin Tocci jtocci at, Tom Lane tgl at, elein at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW