Mark, For these purposes (dynamically properties) we have hstore in PostgreSQL which works very well. Any table in PostgreSQL is a static composite type as well. I don't clearly understand why do you need thousands NULLable columns in a huge tables (which also makes NOT NULL constraints useless) rather than well defined compact table with just one (statically allocated) column of type hstore ? Again I think that hstore ideal solution here.
2010/11/13 Mark Mitchell <mmitch...@riccagroup.com> > These tables are created and maintained automatically by software. A > table may be created with some initial columns and as respondents take a > survey and we collect data each data point is stored in a column and these > columns are created on the fly by the application. > > There is no need to "maintain" it as the software does that for us. While > it may seems like bad practice it has actually worked out very well for us > for years. When we reach the column limit we create sub tables but when > reporting on this data it becomes an issue because you have to know which > data point is in what sub table, that is what I am trying to get around. I > think it's very obvious that Postgres developers have no interest in going > over 1600 columns in the foreseeable future and which forces us to find > creative ways around it but I just don't see why it has to be this way. Even > view which are not even stored entities have this limit. I'm sure there are > good reasons in terms of how the Postgres code works which is way over my > head but when you look at the comparison of database limitations ( > http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Limits > ) it seems that in every area expect column name size and number of > columns postgres has huge advantages over other systems. > > > > *From:* Dmitriy Igrishin [mailto:dmit...@gmail.com] > *Sent:* Friday, November 12, 2010 4:30 PM > *To:* Clark C. Evans > *Cc:* Dann Corbit; Mark Mitchell; Tom Lane; pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] More then 1600 columns? > > > > I can't imagine how to maintain a database with tables with > 1600 columns... I can't imagine how to simple work with this > garbage of data via SQL... > > 2010/11/13 Clark C. Evans <c...@clarkevans.com> > > On Fri, 12 Nov 2010 21:10 +0000, "Dann Corbit" wrote: > > If (for access) the single table seems simpler, then > > a view can be used. > > Even if you "partition" the columns in the instrument > over N tables, you still can't query it in a single > result set. The limit is quite deep in PostgreSQL > and extends to tuples, including views and in-memory > query results. > > I find that partitioning does work, but it requires extra > care on the part of the application developer that really > shouldn't be necessary. > > Best, > > Clark > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > // Dmitriy. > > -- // Dmitriy.
