Re: More then 1600 columns?

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 <mmitchell(at)riccagroup(dot)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:dmitigr(at)gmail(dot)com]
> *Sent:* Friday, November 12, 2010 4:30 PM
> *To:* Clark C. Evans
> *Cc:* Dann Corbit; Mark Mitchell; Tom Lane; pgsql-general(at)postgresql(dot)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 <cce(at)clarkevans(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> // Dmitriy.
>
>

--
// Dmitriy.