If you find that your template1 has unnecessary objects in it, for example, obsolete functions, then you should clean it out. Only objects that you want to have in any new databases that you create should be in template1

The most conservative and sane solution is to look at objects that are not in the pg_catalog schema and explicitly drop any unwanted objects.

A slightly less conservative approach is to change the template designation of template1, drop it and recreate it from template0. You could do this if your template1 is beyond repair.

	=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
	=# DROP DATABASE template1;
	=# CREATE DATABASE template1 WITH template template0;
	=# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';

It was suggested, perhaps in jest, that you could also delete the data directory and do an initdb however this would make you lose all of your existing data so it is NOT recommended.

Contributors: tom at minnesota.com, shridhar_daithankar at persistent.co.in, dan at langille.org, Neil Conway neilc at samurai.com, jochemd at oli.tudelft.nl

Setting NULL representation for copy [GENERAL]Can't copy empty value to datatype 'timestamp' 8-Jan-2003

When you copy data using the copy command you can designate how you are specifying NULL in the file by using:

	copy .... with NULL 'xxx'

The default value of the null designation is '\N' (no quotes). If you use '' as the null designation, it will assume that empty fields are to be null. This may not be correct if your data is supposed to contain empty strings.

The question in hand was about empty fields targeted for timestamp columns. In this case, it was correct to use '' because it did not interfere with other data.

Contributors: Jon Thorson oinos at darkgeek.com shridhar_daithankar at persistent.co.in Stephan Szabo sszabo at megazone23.bigpanda.com

Sorting NULLs on top [GENERAL] Problem of Null Ordering 03-Dec-2002

In the normal course of events, sorting by a nullable column will result in the columns containing nulls to sort after all of the other rows. If you want the nulls first, you could sort in descending order:

	select col from mytable order by col desc; 

To sort in normal order, but with nulls first use:

	select coll from mytable order by col IS NULL DESC, COL 

This example illustrates simply the feature in PostgreSQL where you may use an expression as part of the order by clause.

Contributors: Harry Yau harry.yau at regaltronic.com, SZUCS Gábor surrano at mailbox.hu, Dennis Björklund db at zigo.dhs.org, Jean-Christian Imbeault jc at mega-bucks.co.jp

Referential Integrity Update Trigger? [GENERAL] Trigger once again 28-Nov-2002

The following is a trigger was created to validate the tag_number column for uniqueness on update. However, when columns other than tag_number are updated, they are rejected. When, say, column item_name is updated, both the OLD and the NEW records will have the same value for tag_number and therefore the exception will be raised.

	CREATE OR REPLACE FUNCTION validate_tag_number() 
	RETURNS OPAQUE AS '
		BEGIN
			IF new.tag_number=old.tag_number THEN
				RAISE EXCEPTION ''Number is already present '';
			END IF;
			RETURN NEW;
		END;
	'LANGUAGE 'plpgsql';

	CREATE TRIGGER  validate_tag_number BEFORE UPDATE ON tag_vendor FOR EACH ROW      
		EXECUTE PROCEDURE validate_tag_number();

What you really want to do to ensure uniqueness is to define the column with a unique index by declaring it to be UNIQUE.

Contributors: Adrian Klaver aklaver at attbi.com, Oliver Elphick olly at lfix.co.uk, Tariq Muhammad tmuhamma at libertyrms.com, Tom Lane tgl at sss.pgh.pa.us

Updating from a selection [GENERAL] update with select 09-Jan-2003

The UPDATE statement can take a FROM clause. This enables easy update of one table from the values of any selection. The FROM clause can be a table or a subquery. The following query can be simplified into one query:

	UPDATE product SET 
		price_min = (SELECT min (price) FROM subproduct WHERE subproduct.product = $ID),
		price_max = (SELECT max (price) FROM subproduct WHERE subproduct.product = $ID)
	WHERE id = $ID

The following query is is equivalent to the query above.

	UPDATE product SET 
		price_min = ss.min_price, price_max = ss.max_price 
	FROM (	SELECT min( price) as min_price, max( price) as max_price 
				FROM subproduct 
				WHERE subproduct.product = $ID ) as ss
	WHERE id = $ID;

Contributors: Cristóvão B. B. Dalla Costa cbraga at bsi.com.br, Jean-Luc Lachance jllachan at nsd.ca

Qualifying Aggregates [GENERAL] Using an ALIAS in WHERE clause 28-Nov-2002

You may not use an alias from the target list in your where clause because the where clause is evaluated before the target list. First the rows are qualified, then the rows are fetched.

	-- Incorrect
	SELECT  id, sum(hours) AS totalhours
	FROM mytable
	WHERE totalhours > 50
	GROUP BY id;

You may then try to put the aggregate in the WHERE clause, however, aggregates are not allowed in the WHERE clause. Qualification for aggregation should be specified in the HAVING clause. WHERE filters rows before aggregation and HAVING filters rows after aggregation. The correct query would be either one of the following:

	SELECT  id, sum(hours) AS totalhours
	FROM mytable
	GROUP BY id
	HAVING sum(hours) > 50;

	SELECT * 
	FROM (SELECT  id, sum(hours) AS totalhours
	      FROM mytable
	      GROUP BY id) ss
	WHERE totalhours > 50;

Contributors: Ron St.Pierre rstpierre at syscor.com, Tycho Fruru tycho at fruru.com, Tom Lane tgl at sss.pgh.pa.us, Magnus Naeslund(f) mag at fbab.net

Hardware Crashes -- by Josh Berkus [GENERAL] Total crash of my db-server 15-Dec-2002

Henrik reported occasional total system lock-ups on a production PostgreSQL 7.3 on Red Hat Linux production server. Apparently the machine would have a kernel panic or similar during nightly maintenence which includes VACUUM FULL and REINDEXing.

Respondents were universal in diagnosing the report as a hardware or operating system problem. Several people pointed out that while PostgreSQL could crash itself, it could not crash the host OS unless its activity was exposing an existing hardware or kernel problem.

The most popular diagnosis was bad RAM; seven participants mentioned similar incidents with a variety of systems all related to bad or incompatible RAM. Two people mentioned having seen RAM fail after a significant period of use ... one after 2 years. Other suggested diagnoses included:

• RAID driver or controller bugs
• Other drive and disk access errors
• Bad motherboard
• Poor thermal control (bad fan or thermal monitor)
• Overclocked CPU

Other participants chimed in with a variety of ways to test a new system, which included:

• IBM's memtest86
• Compiling Linux and PostgreSQL, repeatedly with md5sum checks on the result
• Large (> 1GB ) file copies with md5sum checks.
• Turn off air conditioning and check server performance at 75 deg. F.
• cpuburn program ( http://users.ev1.net/~redelm/ )
• Quake II in demo mode for extended periods
• pg_bench run on high settings (10M records + 100 users)

Contributors: Henrik Steffen steffen at city-map.de, Justin Clift justin at postgresql.org, Ian Barwick barwick at gmx.net, Tom Lane tgl at sss.pgh.pa.us, Shridhar Daithankar shridhar_daithankar at persistent.co.in, James Thompson jamest at math.ksu.edu, Nigel J. Andrews nandrews at investsystems.co.uk, Kevin Brown kevin at sysexperts.com, Thomas Beutin tyrone at laokoon.IN-Berlin.DE, scott.marlowe scott.marlowe at ihs.com, Kenneth Godee Ken at perfect-image.com, Lincoln Yeoh lyeoh at pop.jaring.my, Tino Wildenhain tino at wildenhain.de, Lee Harr missive at frontiernet.net, Jan Weerts j.weerts at i-views.de, Brian Hirt bhirt at mobygames.com