Message-ID: <3D11D9FA.F630297D@noao.edu>
Date: Thu, 20 Jun 2002 06:34:50 -0700
From: Steve Wampler <swampler@noao.edu>
Organization: National Solar Observatory
MIME-Version: 1.0
To: postgres-sql <pgsql-sql@postgresql.org>
Subject: Re: Tagging rows into collections?
References: <3D10AAFB.D51B1B66@noao.edu> <7413.1024521571@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> 
> Steve Wampler <swampler@noao.edu> writes:
> >  An event has:  timestamp,event_name,list_of_attributes
> >    The list_of_attributes are simple (string) name,value pairs.
> 
> > However, although selection performance isn't a priority, the
> > ability to reconstruct the events from the database is needed
> > and the above simple table doesn't provide enough information
> > to do so.  (The resolution on the timestamp field isn't
> > enough to distinquish separate events that have the same name.)
> 
> What PG version are you using?  In 7.2 the default timestamp resolution
> is microseconds, rather than seconds.  That might be enough to fix your
> problem.

Still 7.1.  I'd rather not rely on the timestamp resolution, though
that would probably work with 7.2's resolution.

> If not, your two-table approach sounds reasonable.  You could stick
> with one table and use arrays for the name/value columns, but that
> will make searches harder.

I'll try the two-table approach - thanks!

How can I associate <timestamp,event_name> entries in the first
table with <name,value> entries in the second?  Would using the OID
work - and, if so, how?  (Since insertion performance is important,
I'd like to avoid having to do an: [insert <timestamp,event_name>;
query-to-get-id;insert <name,value> sequences].  Is there a way in
SQL to set up the linkage between a row in the first table and
(multiple) rows in the second table?

Thanks!
-- 
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.