Searing array fields - or should I redesign?

From: Bryan Montgomery <monty(at)english(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Searing array fields - or should I redesign?
Date: 2010-12-15 15:13:46
Message-ID: AANLkTikGU7aSn-eD+7u_LLwTLt7Qq_bSfA1r9Z1iQtih@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have a process that is logging data from vehicles. I'm looking for
thoughts on the pros and cons of different approaches to storing this data -
and retrieving it. Different vehicles report different types of data. The
current process stores the data in a multi-dimensional array.

eg, insert into logtable values ( 'vehicle123', now(),
{{'voltage','13'},{'rpm','600'}};

However, I am not sure how I can write a query - for example to read all
records where the voltage field is less than 13. Performance in this case is
not a real significant issue.

Would I be better off redesigning and having a master / detail kind of
structure? Where the master table would have the vehicle id, timestamp and a
key to the detail table. The detail table would then have the key, the type
of measurement and then the value.

I guess a third approach would be to just have the detail table with
duplication on the vehicle id and time - for each data type recorded.

Thanks,
Bryan.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus Moor 2010-12-15 15:15:02 Re: How to restore from backup to 8.4.3 server using 9.0 dump/restore
Previous Message Adrian Klaver 2010-12-15 14:37:42 Re: create language 'plpythonu' on win failed