Skip site navigation (1) Skip section navigation (2)

Re: [GENEAL] dynamically changing table

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: A B <gentosaker(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENEAL] dynamically changing table
Date: 2009-03-31 12:18:17
Message-ID: 437FAA9F-DF2D-429E-9856-EB2026B55940@solfertje.student.utwente.nl (view raw or flat)
Thread:
Lists: pgsql-general
On Mar 30, 2009, at 5:39 PM, A B wrote:

> 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.


Ignoring design implications (you got enough replies about that I  
think)...

You could add the columns you're sure that you need and put the rest  
in an XML field. That field can then contain any custom fields that  
you don't need right away while you still have the data available in a  
useful fashion. If it turns out some of those custom fields should end  
up in the table as a column it isn't hard to extract the data from the  
XML field.
It's probably a good idea to add a version attribute or field to your  
XML that you increment on each model change (an xsd would be even  
better, it allows you to specify types for your data) so that you know  
which fields to expect in the document.

You can query XML fields using xpath expressions in your queries.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49d20a0c129741113880388!



In response to

Responses

pgsql-general by date

Next:From: Raymond O'DonnellDate: 2009-03-31 12:43:30
Subject: Re: Postgres: Packaging & Server Startup
Previous:From: Richard HuxtonDate: 2009-03-31 11:25:29
Subject: Re: Postgres: Packaging & Server Startup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group