[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.
Re: [SQL] Max column number.
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
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 >
Re: [SQL] Max column number.
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 -- 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.
Hi Miguel, maybe you can split table to two tables with one-to-one connection. The another way is to create dynamic-attribute-tables which means to store data in columns, not in rows. On Tue, Jul 12, 2011 at 7:48 PM, Miguel Angel Conte wrote: > 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. > -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] Max column number.
On Tue, Jul 12, 2011 at 12:08 PM, Miguel Angel Conte wrote: > Unfortunately It's an inherited data model and I can't make any change for > now... > Thanks for your answer! when you can change it, look at hstore -- 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.
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 >
[SQL] combining strings to make a query
I'm using Erlang and postgresql to build a web interface. When I create the query string I get something like: select * from table where field::text ilike '%%' But when I do that (if someone types in '\' for part of the text search), I get a pg log entry to use E'\\' How would I use E'' with ilike '%%'. ilike E'%\\%' doesn't work. Thanks, -wes -- 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] interesting sequence
IF this field is unique you shouldn't get duplicates from a function; the transaction will either succeed or fail; the beauty of a function is that you can return an error message. I personally prefer to handle errors at the application level, but if you have admins running ad-hoc queries on the database level doing inserts then you definitely do need to handle that properly. Triggers are handy in that regard. You can do loops to check for collisions...I haven't done that so don't know the best way to code that though. On Wed, Jul 6, 2011 at 5:28 AM, Jasen Betts wrote: > On 2011-07-06, Kevin Crain wrote: >> That's why you need to do this inside a function. Basically just make >> an insert function for the table and have it calculate the count and >> do the insert in one transaction. > > you will still get duplicates, so include code in the function to > retry if there is an error. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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.
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 Leeuwen reinou...@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 >> >> > -- 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] combining strings to make a query
You can do full-text search in postgres now using ts_vectors. I'd recommend going that route. Doing like comparisons is not a good idea if you don't know the first part of the string you are searching forIt appears to be much faster from my experience to search for ab% than it is to search for %ab%. On Tue, Jul 12, 2011 at 7:51 PM, Wes James wrote: > I'm using Erlang and postgresql to build a web interface. When I > create the query string I get something like: > > select * from table where field::text ilike '%%' > > But when I do that (if someone types in '\' for part of the text > search), I get a pg log entry to use E'\\' > > How would I use E'' with ilike '%%'. ilike E'%\\%' doesn't work. > > Thanks, > > -wes > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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 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 Leeuwen reinou...@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 >> >> > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
