Re: Ad-hoc table type?

> pgsql(at)mohawksoft(dot)com writes:
>> Something like this:
>
>> create adhoc table foo ();
>
>> insert into foo (name, rank, serial) values ('joe', 'sargent', '42');
>
>> In an "ad-hoc" table type, when an insert is made, and a column is not
>> found, then a new varchar column is added.
>
>> I know the idea has a lot of holes, and is probably a bad idea, but it
>> answers an important problem of easily mapping programmatic types to a
>> database.
>
> Seems like a table with one contrib/hstore column might be more relevant
> to this guy's idea of how to do database design.
>

That's actually a very cool module, I hadn't seen it before. I've
considered writing something like it, but more XML centric, but I'm not
sure it answers the concept.

I'm not sure if you have dealt with web site sessions and object
persistence crap, but its a pain to get up and running and improving
performance is a drag. Web guys tend to know very little about databases
and tend, sadly, not to be very inquisitive about such things.

Web session and user attribute objects are typically stored in a database
as XML, JSON, or some other aggregated format in a single column (hstore).
That works great for when you just need to access the data by the key, but
if you want to "use" the data outside the web application for something
like OLAP, you have to decide which attributes reside in the aggregate
column or get promoted to a full fledged column. That's why you'll see
tables with username, passwdhash, email, etc. in addition to an aggregated
column of things like screen template, age, etc.

So, how do you have a table of a generally arbitrary number of columns
without creating some sort of aggregate column? With an aggregate column,
the data isn't on the same level as real column data, so you need to parse
the aggregate to extract a value, and you have to do that for each value.
On top of that, you then have to explain your aggregate strategy to the
web guys.

Being able to insert arbitrary named values, and extracting them
similarly, IMHO works "better" and more naturally than some external
aggregate system built on a column. I know it is a little "outside the
box" thinking, what do you think?