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

02-Dec-2002 Issue: 2

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

Congratulations to the PostgreSQL Global Development Group
Announcing Version 7.3! 28-Nov-2002

Source for this release is available at:

More information on PostgreSQL is available in nine languages on the PostgreSQL
Advocacy website:

A complete list of changes in PostgreSQL version 7.3 can be found in the HISTORY
file included with the release, or available on the web at:
Contributors: PostgreSQL Global Development Group
Using the to_char() formatting function
[GENERAL] Formatting psql output 06-Nov-2002

To format the of digits represented in a result set of numbers, use to_char(). If the size of your number is greater than what will fit into the mask, the digits are represented as '#' to show overflow. For example:

	# select to_char( 12345.67, '999G999D99S');
	(1 row)

	# select to_char( 12345.67, '99D99S');
	(1 row)

Contributors: markMLl.pgsql-general at, Bruce Momjian pgman at, Tom Lane tgl at, Bruno Wolff III bruno at
[GENERAL] command 08-Nov-2002

Sending the output of the postmaster to stdout will not give you a log file. You must not only capture stdout but stderr.

	WRONG: postmaster -i -D .... -d 10 > logfile 
	RIGHT: postmaster -i -D .... -d 10 > logfile 2> &1 

Using the pg_ctl with the -l option is easier. Many people use a shell wrapper on pg_ctl with the command line options to start the server consistently, for example:

	pg_ctl start -D $PGDATA -l $PGDATA/pglog -o "-i"

You can also set the appropriate debug levels and features in postgres.conf. These are documented in the 7.2 PostgreSQL manual under Section 3 Server Runtime Environment Section 4 Run-time configuration.

The LOG_TIMESTAMP option in postgres.conf will put a timestamp on the server log messages.

You can also use your system's logging features. In this example for Linux make changes to postgres.conf and /etc/syslog.conf.

	syslog          = 1 # range 0-2
	syslog_facility = 'LOCAL0'
	syslog_ident    = 'postgres'

	local0.*        /var/log/postgresql
Then do a restart of both syslog and postgres:
	/etc/init.d/syslog restart
	/etc/init.d/postgresql restart
Contributors: Florian Litot flitot at, Shridhar Daithankar shridhar_daithankar at, Lee Kindness lkindness at, Neil Conway neilc at, Tom Lane tgl at, Medi Montaseri medi.montaseri at
Copy errors indicate improper (dos) file format
[GENERAL] Error on import 24-Oct-2002

copy gives import errors on files with dos carriage returns (\r\n) instead of unix carriage returns (\n). You can fix this using vi or vim by editing the file and typing:

	:set fileformat=unix
and write out the file. But there are also (too? :-) many ways to fix this in Perl. Here are three suggestions:

	cat origfile|perl -e 'while (<>){$_ =~ s/\r//g;print $_;}' > prepfile
	perl -i -pe 's/\r\n/\n/' filename
	perl -p -e 's/\r//g' < origfile > prepfile

Contributors: John johnp at, Tom Lane tgl at, Medi Montaseri medi.montaseri at, Richard Huxton dev at, elein at
Retrieve the latest record by date or timestamp
[GENERAL] newbie question for return date 28-Oct-2002

To retrieve the latest record in a table with a date or timestamp, the concensus on the best way is

where there is an index on d.

Contributors: tviardot tviardot at, Lee Harr missive at, Medi Montaseri medi.montaseri at, Tom Lane tgl at, terry at, Richard Huxton dev at
pgtcl: Data Manipulation counts
[GENERAL] number of affected rows in pgtcl 24-Oct-2002

In pgtcl when using pg_exec(), pg_result does not provide the number of rows updated, deleted or inserted. You should use pg_execute() instead of pg_exec(). pg_execute() has just been documented in PostgreSQL 7.3, however, it has been available since 7.1.

Contributors: Christian Traber christian at, Tom Lane tgl at
[GENERAL] Unwanted Log Entries
[GENERAL] Unwanted Log Entries 22-Nov-2002

The log entries

are caused by the client issuing the statement:

You can remove that statement from your client to remove those messages. If you cannot edit your client, you must move to 7.3. In 7.3 SHOW commands will no longer generate log entries.

Contributors: Jon Swinth jswinth at, Tom Lane tgl 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