Re: [SQL] Max column number.
I have the metadata in the same csv. On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: > How are you determining the data types for these columns? > > On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte > wrote: > > Hi, > > Thanks for your interest. This app load scv files which change every day > > (sometimes the columns too). The sizes of these files are in avg 15MB. > So, > > We load something like 100MB each day. We tried to find a better solution > > but we couldn't, becouse one of the our requirement is not to use a lot > of > > space. Also, the app is used to consult these information, and for our > > particular type of select's queries, we get the best performance if the > > information is all into a same row. > > > > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain > wrote: > >> > >> I still can't imagine why you'd ever need this...could you explain > >> what this does? I'm just curious now > >> > >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain > >> wrote: > >> > This is an unfortunate situation, you shouldn't be required to do > >> > this, the people generating your requirements need to be more > >> > informed. I would make damn sure you notify the stakeholders in this > >> > project that the data model is screwed and needs a redesign. I agree > >> > that you should split this table and do a join if you have no option > >> > of redesigning this. > >> > > >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte < > diaf...@gmail.com> > >> > wrote: > >> >> Yes, sure. I mean, I can't change the whole process which creates > >> >> columns > >> >> dynamically. > >> >> > >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen > >> >> wrote: > >> >>> > >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > >> >>> > Unfortunately It's an inherited data model and I can't make any > >> >>> > change > >> >>> > for > >> >>> > now... > >> >>> > >> >>> but by adding columns you *are* making changes to it... > >> >>> > >> >>> Reinoud > >> >>> -- > >> >>> __ > >> >>> "Nothing is as subjective as reality" > >> >>> Reinoud van Leeuwenreinou...@n.leeuwen.net > >> >>> http://reinoud.van.leeuwen.net kvk 27320762 > >> >>> __ > >> >>> > >> >>> -- > >> >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> >>> To make changes to your subscription: > >> >>> http://www.postgresql.org/mailpref/pgsql-sql > >> >> > >> >> > >> > > > > > >
Re: [SQL] Max column number.
I can't drop the table. I have to add as many columns as posible and when I exceed the limit I have to create another table. I've tried normalizing but then the join's cost is too big. I always need to use all columns, so getting a all information into a single row it's the most efficient solution. On Wed, Jul 13, 2011 at 1:51 PM, Scott Marlowe wrote: > On Wed, Jul 13, 2011 at 9:45 AM, Miguel Angel Conte > wrote: > > Hi, > > Thanks for your interest. This app load scv files which change every day > > (sometimes the columns too). The sizes of these files are in avg 15MB. > So, > > We load something like 100MB each day. We tried to find a better solution > > but we couldn't, becouse one of the our requirement is not to use a lot > of > > space. Also, the app is used to consult these information, and for our > > particular type of select's queries, we get the best performance if the > > information is all into a same row. > > This definitely sounds like a job for something like hstore. > > Also, can you drop and recreate the table every so often? That would > certainly help. >
Re: [SQL] Max column number.
Hi, Thanks for your interest. This app load scv files which change every day (sometimes the columns too). The sizes of these files are in avg 15MB. So, We load something like 100MB each day. We tried to find a better solution but we couldn't, becouse one of the our requirement is not to use a lot of space. Also, the app is used to consult these information, and for our particular type of select's queries, we get the best performance if the information is all into a same row. On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote: > I still can't imagine why you'd ever need this...could you explain > what this does? I'm just curious now > > On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain > wrote: > > This is an unfortunate situation, you shouldn't be required to do > > this, the people generating your requirements need to be more > > informed. I would make damn sure you notify the stakeholders in this > > project that the data model is screwed and needs a redesign. I agree > > that you should split this table and do a join if you have no option > > of redesigning this. > > > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte > wrote: > >> Yes, sure. I mean, I can't change the whole process which creates > columns > >> dynamically. > >> > >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen > >> wrote: > >>> > >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > >>> > Unfortunately It's an inherited data model and I can't make any > change > >>> > for > >>> > now... > >>> > >>> but by adding columns you *are* making changes to it... > >>> > >>> Reinoud > >>> -- > >>> __ > >>> "Nothing is as subjective as reality" > >>> Reinoud van Leeuwenreinou...@n.leeuwen.net > >>> http://reinoud.van.leeuwen.net kvk 27320762 > >>> __ > >>> > >>> -- > >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >>> To make changes to your subscription: > >>> http://www.postgresql.org/mailpref/pgsql-sql > >> > >> > > >
Re: [SQL] Max column number.
Yes, sure. I mean, I can't change the whole process which creates columns dynamically. On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen < reinou...@n.leeuwen.net> wrote: > On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > > Unfortunately It's an inherited data model and I can't make any change > for > > now... > > but by adding columns you *are* making changes to it... > > Reinoud > -- > __ > "Nothing is as subjective as reality" > Reinoud van Leeuwenreinou...@n.leeuwen.net > http://reinoud.van.leeuwen.net kvk 27320762 > __ > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Max column number.
> > Hi Ken, > > Do you know a good way to get the max row size in a table? > Or maybe I'll have to get this information from the metadata > > thanks! > > > On Tue, Jul 12, 2011 at 3:11 PM, k...@rice.edu wrote: > >> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> > Unfortunately It's an inherited data model and I can't make any change >> for >> > now... >> > Thanks for your answer! >> > >> > On Tue, Jul 12, 2011 at 2:52 PM, Reinoud van Leeuwen < >> > reinou...@n.leeuwen.net> wrote: >> > >> > > On Tue, Jul 12, 2011 at 02:48:26PM -0300, Miguel Angel Conte wrote: >> > > >> > > > Something like: >> > > > "If I'm not going to exceed the size limit, then I can add a new >> column" >> > > >> > > You want to add columns in your application? Are you sure you have the >> > > right datamodel? >> > > >> > > Reinoud >> > > -- >> Hi Reinoud, >> >> I think you will need to write a function to grovel around in the database >> to determine what the size of the row would be given a definition. Tedious >> but straight-forward. >> >> Regards, >> Ken >> > >
Re: [SQL] Max column number.
Unfortunately It's an inherited data model and I can't make any change for now... Thanks for your answer! On Tue, Jul 12, 2011 at 2:52 PM, Reinoud van Leeuwen < reinou...@n.leeuwen.net> wrote: > On Tue, Jul 12, 2011 at 02:48:26PM -0300, Miguel Angel Conte wrote: > > > Something like: > > "If I'm not going to exceed the size limit, then I can add a new column" > > You want to add columns in your application? Are you sure you have the > right datamodel? > > Reinoud > -- > __ > "Nothing is as subjective as reality" > Reinoud van Leeuwenreinou...@n.leeuwen.net > http://reinoud.van.leeuwen.net kvk 27320762 > __ > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Max column number.
Hi, I'm using postgresql 9 and I'd like to know if there is a way to "ask" if when I'm going to add a column, I'm exceeding the max number of columns. I've found that the max number of columns is 1600 and It's depends of the data types. I've made a test adding 1600 columns using different data types and I didn't have any problem. The problem was when I tried to insert data. ERROR: row is too big: size 8168, maximum size 8160 I know that the row limit size is 8k by default, but I need a way to prevent this exception. For example, my table have 600 columns and I insert a row with values for each column and the row size is 8000. So, in this case If I want to prevent errors, I can't add more columns, but to do that I need to know about this limit situation. Something like: "If I'm not going to exceed the size limit, then I can add a new column" Thanks in advance, Miguel.
