Re: Temporal data storage

On Mon, 2010-04-05 at 18:28 +0200, Gerhard Heift wrote:
> create type period as (
> since timestamptz,
> "until" timestamptz
> );

Please take a look at:

http://pgfoundry.org/projects/temporal

That may be a more useful type for you, and it's also called "PERIOD".

> with rows:
> + a gist index already exists (must be modified)
> + can add other attributes to the valid time
> - prevent overlapping is very complex

See Exclusion Constraints (in upcoming 9.0 release):

http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/

> - binary operations like "and", "or" and "not" operates on multiple rows
>
> with array:
> + overlapping can simply done with a constraint and a function

I believe that you mean "preventing overlap within the array", and
you're correct. However, if you want to prevent overlapping between two
tuples, you need to use Exclusion Constraints.

Regards,
Jeff Davis