Search Postgresql Archives

Re: Relational database design book

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



It might be useful to look at the capabilities of the Informix Timeseries Datablade

(http://www-01.ibm.com/software/data/informix/blades/)

if you want to look at ways of enhancing the temporal data capabilities of Postgres.

Cheers,

  Brent

 

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Chris Browne <cbbrowne@xxxxxxx> 12/16/08 10:05 AM >>>
rshepard@xxxxxxxxxxxxxxx (Rich Shepard) writes:
>
> [2] Strangely enough -- to me, at least -- the lack of full support for
> date- and time-based SQL in database tools such as PostgreSQL is puzzling.
> Virtually all business-related databases (think accounting systems as a
> prime example) depend on dates. So do many scientific databases.

The support for temporality in PostgreSQL seems above average as far
as I can see...

PostgreSQL has pretty nice time types between the timestamptz type and
interval.

What strikes me as being missing is the ability to create
temporally-aware foreign keys.

That is, suppose the schema is:

create table1 (
   nearly_pk integer not null,
   from_date timestamptz not null default now(),
   to_date timestamptz not null default 'Infinity',
   constraint dating_t1 check (from_date < to_date)
   -- probably some other data...
);

I'd like to be able to do two more things:

a) Treat the date range as part of the primary key (which isn't
forcibly hard),

b) Have references to table1 that point to the time range for the
"nearly_pk" value but which are a little more liberal with the dates.

create table2 (
   t2pk integer primary key,
   nearly_pk integer not null,
   from_date timestamptz not null default now(),
   to_date timestamptz not null default 'Infinity',
   -- And have a "foreign key" that requires that 
   --  for tuple in table2 the combination (nearly_pk, from_date, to_date)
   --  is *contained* by relevant ranges of (nearly_pk, from_date, to_date)
   --  on table1
   foreign key (nearly_pk) references
            table1(nearly_pk) with temporal (table2.from_date, table2.to_date)
            contained_by (table1.from_date, table1.to_date) 
);

I don't think the syntax there is necessarily quite right; I'm just
hoping to express the idea successfully.

I could presumably do this with a trigger; have been trying to avoid
that thus far.

There are, of course, other ways of treating temporality; that is part
of why it's early to treat this approach as worth putting into syntax.
-- 
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/finances.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."  
-- Microsoft Word for Windows 2.0 User's Guide, p.35:

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux