Re: Ad-hoc table type?

pgsql(at)mohawksoft(dot)com writes:
> 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?

I'm failing to see the point. Allowing columns to spring into existence
without any forethought seems to me to be all minuses and no pluses
worth mentioning.

* What if the column name is just a typo?

* What datatype should it have? ("Always varchar" is just lame.)

* Should it have an index? If so, should it be unique?

* If you keep doing this, you'll soon find yourself reading out
unbelievably wide tables (lots of columns), which won't be especially
easy or efficient to process on either the backend or the client side.
Plus you might run into the max-columns-per-tuple limit.

If you've expended enough thought to be sure that the column is not just
a typo, ISTM that you can afford to enter an ALTER TABLE ADD COLUMN
command to tell the database the results of your genius.

I do see the point that switching from "member of an hstore column" to
"real database column" is pretty painful, but I don't see that "allow
columns to spring into existence" solves that in any meaningful way.
Is there some other way we could address such conversions?

BTW, I think it is (or should be) possible to create an index on
hstore->'mycol', so at least one of the reasons why you should *need*
to switch to a "real" database column seems bogus.

regards, tom lane