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::intervalwhere textstring is of the form:
ascii_number units direction [textstring...]units may be one of:
second, minute, hour, day, week, month, year, decade, century, millenniumdirection may empty or ago.
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'However, there are several right ways to do it.
Cast it in the subselect by creating the interval string there:
SELECT current_timestamp + (select (no_days::text || 'days')::interval from projects);Another way is to rely on the flexibility of the implicit casts with multiplication. This method is more easily understood when read and is more efficient.
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 rugbyIgnore the schemas pg_toast and pg_temp_n. These are internally used schemas. In my test database, there are several schemas besides public and pg_catalog: foo, rr, ere, and 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';Remember, however, that system catalog changes, even simple ones like this can go wrong easily. Be careful.
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)To select the contents of a table just qualify the table name with the prefix of the schema:
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;Notice, however, that if the two schemas have tables of the same name, the table from the leftmost schema will be used. Once search_path is set, then you can do an unqualified \d to get a list of tables:
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)
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:
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.
This is what some folks had to say on the merits of running PostgreSQL on EXT2, EXT3, JFS, XFS, ReiserFS filesystems.
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 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;The table will be locked exclusively for the reindexing.
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.
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.
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