Some examples of time interval data include 6 days, 2 days 14 minutes, 5 hours, 16 years . Each requires an amount of time and an indicator of what time unit it represents. The interval function takes one string argument containing information made up of one or more pairs of an amount and a unit. interval is not an ordinary cast function. Cast functions usually require only one piece of information, not two or more and in this case it can be confusing because the two bits of information must be in a single text string.

The syntax for the interval function is:

	interval [(] textstring [)];
	or
	textstring::interval

	ascii_number units direction [textstring...]

	second, minute, hour, day, week, month, year, decade, century, millennium

Construct the text string described above to create an interval data type. For example, if there is a project table containing an estimated duration in days stored as an integer, you may ask, "If we start now, what will the end date of the project be?" The duration data, an integer, must be fetched by selecting it from the project table as an interval of days and added to the current timestamp.

This is one wrong way. Subselects cannot be embedded into a string.

	SELECT current_timestamp + interval '(select no_days from projects ) days'

Cast it in the subselect by creating the interval string there:

	SELECT current_timestamp + 
		(select (no_days::text || 'days')::interval from projects);

	SELECT current_timestamp + ( (select no_days from projects) * interval '1 day') ;

Now that schemas are working well in 7.3, there are some basic tasks that need to be handled. To find out what schemas are in the database using 7.4 you will be able to issue \dn in psql, but for now in 7.3 you can use:

	elein=# select nspname from pg_namespace;
  	nspname
	------------
 	pg_catalog
 	pg_toast
 	public
 	pg_temp_1
 	sinbin
 	foo
 	pg_temp_3
 	rr
 	ere
 	rugby

To alter a schema name with the ALTER statement you will also have to wait until 7.4. In the meanwhile you can issue this statement as superuser (postgres):

	UPDATE pg_namespace SET nspname='newfoo' WHERE nspname='foo';

To list the tables in a schema, the simplest is to use the schema to qualify the name:

elein=# \dt rr.*
           List of relations
 Schema |     Name     | Type  | Owner
--------+--------------+-------+-------
 rr     | foo          | table | elein
 rr     | rr_appgroups | table | elein
 rr     | rr_prompts   | table | elein
 rr     | rr_reports   | table | elein
 rr     | rr_rprompts  | table | elein
 rr     | rr_types     | table | elein
(6 rows)

	select * from rr.foo; 

To access certain schemas by default, use the SQL variable search_path. By default, search_path is set to the schema with the user's name and public because it is assumed a user's default schema, like their default database name, will be their user name. You can reset search_path for your SQL session or you can put it into your .psqlrc file. For example, if I wanted to only see the schemas rr and rugby, I would use:

	SET search_path TO rr, rugby; 

	elein=# \d
	               List of relations
	 Schema |        Name        |   Type   | Owner
	--------+--------------------+----------+-------
	 rr     | foo                | table    | elein
	 rr     | report_report      | view     | elein
	 rr     | rr_appgroups       | table    | elein
	 rr     | rr_prompts         | table    | elein
	 rr     | rr_reports         | table    | elein
	 rr     | rr_rprompts        | table    | elein
	 rr     | rr_types           | table    | elein
	 rugby  | areas              | table    | elein
	 rugby  | events             | table    | elein
	 rugby  | teams              | table    | elein
	 rugby  | teams_v            | view     | elein
	 rugby  | tmatches           | table    | elein
	 rugby  | tmatches_matid_seq | sequence | elein
	 rugby  | ttypes             | table    | elein
	 rugby  | videos             | table    | elein
	 rugby  | videos_vidid_seq   | sequence | elein
	(16 rows)
	

Contributors: Dan Delaney ddelaney at powercreative.com, Tariq Muhammad tmuhamma at libertyrms.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com

7.4 hopes 7.4 24-Feb-2003

There have been some questions regarding the status of 7.4 in order for people to plan upgrades. The general concensus seems to be that if you have not yet upgraded to 7.3, you should do so in order to facilitate conversion to 7.4.

The three key features that may be in 7.4 are:

• Point in time recovery
• Standard Replication
• Native Windows Port

The ToDo list (as of 18-Feb-2003) targets the following features for 7.4:

* -Add GUC variables to control floating number output digits 
* -Make a transaction-safe TRUNCATE 
* -Add ALTER TABLE tab SET WITHOUT OIDS 
* -Allow CLUSTER to cluster all tables 
* -MOVE 0 should not move to end of cursor 
* -Make PL/PgSQL %TYPE schema-aware
* -Add schema, cast, and conversion backslash commands to psql 
* -Allow pg_dump to dump a specific schema 
* -Support statement-level triggers 
* -Add hash for evaluating GROUP BY aggregates 
* -Make IN/NOT IN have similar performance to EXISTS/NOT EXISTS 
* -Inline simple SQL functions to avoid overhead 
* -Get faster regex() code 
* -Add OpenBSD's getpeereid() call for local socket authentication

The full ToDo list is always available at http://developer.postgresql.org/todo.php. Bruce Momjian does a great job keeping it up to date.

Contributors: Dmitry Tkach dmitry at openratings.com, Dennis Gearon gearond at cvc.net, Bruno Wolff III bruno at wolff.to, Ericson Smith eric at did-it.com, Robert Treat xzilla at users.sourceforge.net, Joe Tomcat tomcat at mobile.mp, Ed L. pgsql at bluepolka.net, Neil Conway neilc at samurai.com, Peter Eisentraut peter_e at gmx.net, Andrew Sullivan andrew at libertyrms.info, Bruce Momjian pgman at candle.pha.pa.us

Filesystems for PostgreSQL [GENERAL] What filesystem? 21-Feb-2003

This is what some folks had to say on the merits of running PostgreSQL on EXT2, EXT3, JFS, XFS, ReiserFS filesystems.

ext2:

• ext2 is really not crash-safe. I think I'd think twice
• EXT2 is the slowest but very very stable.
• I'd be quite surprised if ext2 was any slower than ext3.
• For large smaller directory structures, ext2 is pretty good, but fades as your fs grows.

• EXT3 is good as long as you are running 2.4.20 + the source EXT3 patches.

• There have been some recent reports about corruption under reiserfs, but not much detail on it.
• ReiserFS is good,
• Since most PostgreSQL disk I/O involves large files, I wouldn't think ReiserFS would outperform ext2 either but it does ~30-60% on a single IDE drive. Apparently tree indexes in reiser helps it a lot. This is especially true in file systems with lots of small to medium files.

• bulletproof on IRIX
• JFS and XFS are the most thoroughly tested.
• As for XFS on linux, I'd guess it's probably pretty good, Demo on linux a few years ago was truly amazing.

1. Edit $PGDATA/pg_hba.conf and change ALL authentication types to 'md5'. This means that not even the pgsql user (default postgres) running locally can access the database without a password.

2. However, now your vacuumdb cronjob and your backups won't work without a password and you cannot even start up postgres without a password.

3. The solution is this:

Put a .pgpass file in the pgsql user's (postgres') home directory.
In it put the proper values for each colon separated field:

host:port:database:username:password

Then chmod 600 the file. (Don't forget this step.)

Contributors: Christopher Kings-Lynne chriskl at familyhealth.com.au

Indexing Toast [GENERAL] In 7.3.1, will I be able to reindex toast? 14-Feb-2003

In release 7.3 you can indeed reindex the toast tables directly. To do so use the following, where the schema is pg_toast and the table name is pg_toast_nnnnn.

	REINDEX TABLE pg_toast.pg_toast_840608; 

There was also some discussion about the reindexing of tables within the pg_catalog schema. The key issue about changing those tables is that a few of them are usually fully in the system cache and would need to be cleared from the cache in order to get the benefits of the reindexing.

Contributors: Jeffrey Baker jwbaker at acm.org, Bruce Momjian pgman at candle.pha.pa.us, Mario Weilguni mweilguni at sime.com, Tom Lane tgl at sss.pgh.pa.us, Hiroshi Inoue Inoue at tpf.co.jp

pgsql-announce and PostgreSQL Weekly News [ANNOUNCE] PostgreSQL Weekly News 03-Mar-2003

I have recommended before that it is fairly important to subscribe to pgsql-announce in order to stay on top of events affecting PostgreSQL. But there is an added bonus for subscribing--you will also receive Robert Treat's PostgreSQL Weekly News which covers weekly HACKER progress as well as other timely events. It evens reminds you to read PostgreSQL General Bits :-) What a deal!

To subscribe to pgsql-announce or other pgsql mailing lists, go to the PostgreSQL Mailing List Subscription Page.

Editor: elein at varlena.com