To find the bounding days of a week in PostgreSQL use the date and time functions. There are probably several different ways to find the bounding days of a week by either the week of the year (from EXTRACT) or from the current date. In this example we will find the bounding Sunday and Saturday dates for a given date.

The Sunday of a week is the date minus the day of the week. The Saturday of a week is the date plus seven minus the day of the week minus one. (I found it easier to subtract six from the day of the week.)

	create or replace function sunday_week(timestamptz)
	returns date as
	'
	DECLARE
	   dy integer;
	   ret date;
	BEGIN
	   dy := extract( ''dow'' from $1 );
	   ret := $1 - ( dy || '' days '')::interval;
	   return ret::date;
	END;
	' language 'plpgsql';

	create or replace function saturday_week(timestamptz)
	returns date as
	'
	DECLARE
	   dy integer;
	   ret date;
	BEGIN
	   dy := 6 - extract( ''dow'' from $1 );
	   ret := $1 + ( dy || '' days '')::interval;
	   return ret::date;
	END;
	' language 'plpgsql';

For the current week we have:

	=# select sunday_week(now());
	 sunday_week 
	-------------
	 2004-03-28
	(1 row)
	
	=# select saturday_week(now());
	 saturday_week 
	---------------
	 2004-04-03
	(1 row)

	=# select sunday_week('4/6/04'::timestamp);
	 sunday_week 
	-------------
	 2004-04-03
	(1 row)
	
	=# select saturday_week('4/6/04'::timestamp);
	 saturday_week 
	---------------
	 2004-04-10
	(1 row)

I ran some more tests and peered at my calendar carefully. None of the other weeks failed--just the week of 4/6/04. Then I realized that Saturday, April 03, 2004 in my timezone we change from Pacific Standard Time to Pacific Daylight Time.

I reran the functions and found that 4/6/04 00:59 gave me a sunday_week of 4/3/04 and 4/6/04 01:00 gave me a "correct" sunday_week of 4/4/04.

	=# select sunday_week('4/6/04 00:59'::timestamp);
	 sunday_week 
	-------------
	 2004-04-03
	(1 row)
	=# select sunday_week('4/6/04 01:00'::timestamp);
	 sunday_week 
	-------------
	 2004-04-04
	(1 row)

Be careful what you ask for.

Contributors: Stephen Quinney stephen.quinney at computing-services.oxford.ac.uk elein at varlena.com

General Advice for Upgrading from 7.2.3 to 7.4 [GENERAL] 7.2.3-7.4.2 migration 01-Apr-2004

In order to do any upgrade, first you read the release notes. Then you read the release notes. Again. Really. It will save you a lot of grief. The release notes lay out what things have changed, giving you fair warning for changes you may have to make in any of your own code.

Once you have read the release notes, it is possible to do the dump and restore in one step. It is not necessary to upgrade from 7.2.3 to 7.3 and then again to 7.4.

pg_dump is a stand alone program. You should always try to use the latest greatest version of pg_dump to dump your old database. You can even go as far as using the CVS head version of pg_dump although the 7.4 version is probably adequate. (If you do use CVS head version, you must also use the "-X disable-dollar-quoting" option.)

The later versions of pg_dump have fixes to bugs in old versions and much better dependency ordering. This is key to a smooth migration. If you are restoring across versions, the -i flag to pg_restore ignores version mismatches.

If you have the time (and perhaps you should make the time), you can dump the schema and the data separately. Then load the schema and address any problems and changes you may have. Then drop the database and reload the whole thing with a full restore.

In order to speed up the load, it is recommended that you turn off the triggers using the --disable-triggers option to pg_restore. This is only appropriate for perfectly clean data, of course.

Contributors: Christophe Musielak cmusielak at akio-software.com, Richard Huxton dev at archonet.com, Alvaro Herrera alvherre at dcc.uchile.cl Tom Lane tgl at sss.pgh.pa.us

NULLS are cheap [GENERAL] Storage cost of a null column 02-Apr-2004

When a column in a row is NULL, a bit is set in the NULL indicator for that row. The value in the row is never accessed or checked if the NULL indicator is set.

If a row has no NULL values, there is no NULL indicator for that row. The row NULL indicator, if it exists, will hold all of the bits indicating NULL or NOT NULL for each column.

Contributors: Randall Skelton skelton at brutus.uwaterloo.ca, Martijn van Oosterhout kleptog at svana.org, Tom Lane tgl at sss.pgh.pa.us

The PostgreSQL BSD License [GENERAL] License on PostgreSQL 26-Mar-2004

The software license for PostgreSQL is the The Modified BSD license. This license allows for:

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products derived from this software without specific prior written permission.

The Modified BSD license is not the same as the GNU General Public License, also known as "Copyleft" in that the Modified BSD does not require you to make your derivative code available with the same licensing as the PostgreSQL code.

The The Original BSD license contained the "obnoxious BSD advertising clause." This clause required inclusion of an acknowledgment to the University of California in all advertisements or use of the programs. The clause was rescinded by the University of California in 1999 and no longer pertains to PostgreSQL.

NOTE: NO ONE CONTRIBUTING TO THIS ARTICLE IS A LAWYER. INFORMATION SHOULD BE CONSTRUED AS A LAY PERSON'S GENERAL KNOWLEDGE AND NOT LEGAL ADVICE. IF YOU HAVE ISSUES REGARDING THE LICENSING OF SOFTWARE, IT IS RECOMMENDED THAT YOU DISCUSS THE OPTIONS WITH A LAWYER.

Contributors: Eric Yum eric.yum@ck-lifesciences.com, Bernard Clement bernard@info-electronics.com, David Garamond lists@zara.6.isreserved.com, Tom Lane tgl@sss.pgh.pa.us