FW: [GENEAL] dynamically changing table

From: michaelblack75052(at)hotmail(dot)com
To: gentosaker(at)gmail(dot)com
Subject: RE: [GENERAL] [GENEAL] dynamically changing table
Date: Mon, 30 Mar 2009 16:05:52 +0000

The simplest way is to plan for the maximum number of columns that will be required (say 14 - 2 weeks of data assuming that is daily reporting numbers in the columns. You could have only a single data column and in the first record insert the number of columns that need to be processed, and build the data in the method to load an array to simulate a "record" object for that number. Then process cor the lenghth of the array.

Or you could use the Drop table and Create table instead of Delete data and Alter Table. Also by varying the number of columns you have programming considerations in addition. The the input and process meths will need to check the meta data to determine how many columns it is dealing with.

Those are just to options that come to mind.

Michael

> Date: Mon, 30 Mar 2009 17:39:19 +0200
> Subject: [GENERAL] [GENEAL] dynamically changing table
> From: gentosaker(at)gmail(dot)com
> To: pgsql-general(at)postgresql(dot)org
>
> Hi,
> In the next project I'm going to have a number of colums in my tables,
> but I don't know how many, they change. They all use integers as
> datatype though.. One day, I get 2 new columns, a week later I loose
> one column, and so on in a random pattern.
>
> I will most likely have a few million rows of data so I just wonder if
> there are any problems with running
> alter table x add column .....
> or
> alter table x drop column .....
>
> Adding a column, will it place data "far away" on the disc so that
> select * from x where id=y will result in not quite optimal
> performance since it has to fetch columns from a lot of different
> places?
> Will deleting a column result in a lot of empty space that will anoy
> me later on?
>
> Are there any other clever solutions of this problem?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general