Search Postgresql Archives

Re: Putting many related fields as an array

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

 



On Tue, May 12, 2009 at 7:06 AM, Ow Mun Heng <ow.mun.heng@xxxxxxx> wrote:
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
On Tue, May 12, 2009 at 01:23:14PM +0800, Ow Mun Heng wrote:
>> | sum of count | sum_of_count_squared | qty | qty < 100 | qty < 500 |
>>
>>
>> I'm thinking of lumping them into 1 column via an array instead of into
>> 5 different columns. Not sure how to go about this, hence the email to
>> the list.

>The normal array constructor should work:
>
>  SELECT ARRAY[MIN(v),MAX(v),AVG(v),STDEV(v)]
>  FROM (VALUES (1),(3),(4)) x(v);
>
>Not sure why this is better than using separate columns though.  Maybe a
>new datatype and a custom aggregate would be easier to work with?

The issue here is the # of columns needed to populate the table.

The table I'm summarizing has close to between 50 to 100+ columns, if the
1:5x is used as a yardstick, then the table will get awfully wide quickly.

I need to know how to do it first, then test accordingly for performance and
corner cases.


I apologize for coming into this conversation late.   I used to do analysis of a public use data flat file that had one row per patient and up to 24 diagnosis codes, each in a different column.  Is this analogous to your situation?  I found it was worth the effort to convert the flat file into a relational data model where the patients' diagnosis codes were in one column in a separate table.  This model also makes more complex analysis easier.

Since there were several types of fields that needed to be combined into their own tables, I found it took less time to convert the flat file to the relational model using a script prior to importing the data into the database server.  A Python script would read the original file and create 5 clean, tab-delimited files that were ready to be imported.

I hope this helps.

Andrew

[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