Re: More then 1600 columns? - Mailing list pgsql-general

From Mark Mitchell
Subject Re: More then 1600 columns?
Date
Msg-id 6ef358db-5b57-4c31-a1fe-6083f5225215@riccagroup.com
Whole thread Raw
In response to Re: More then 1600 columns?  (Dmitriy Igrishin <dmitigr@gmail.com>)
Responses Re: More then 1600 columns?
Re: More then 1600 columns?
List pgsql-general

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

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: More then 1600 columns?
Next
From: Dmitriy Igrishin
Date:
Subject: Re: More then 1600 columns?