SEO

Hi All.

I have a question about the Maximum number of columns in a table ?

In FAQ for PostgreSQL,I can find this description :
Maximum number of columns in a table?
250-1600 depending on column types
But , I want to know what type is 1600 limit , and what type is 250
limit . it is important for me , thanks .

Regards ,
zhao xin



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

zhaoxin wrote:
> Hi All.
>
> I have a question about the Maximum number of columns in a table ?
>
> In FAQ for PostgreSQL,I can find this description :
> Maximum number of columns in a table?
> 250-1600 depending on column types
> But , I want to know what type is 1600 limit , and what type is 250
> limit . it is important for me , thanks .

Have you tried creating the columns you need in the type you need?

What happens?

Are you sure hundreds of columns in a table is the best design for your
problem?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I have to face this trouble , it is not I want , but a historical problem .
so , can you tell me ?

Richard Huxton wrote:
> zhaoxin wrote:
>
>>Hi All.
>>
>>I have a question about the Maximum number of columns in a table ?
>>
>>In FAQ for PostgreSQL,I can find this description :
>> Maximum number of columns in a table?
>> 250-1600 depending on column types
>>But , I want to know what type is 1600 limit , and what type is 250
>>limit . it is important for me , thanks .
>
>
> Have you tried creating the columns you need in the type you need?
>
> What happens?
>
> Are you sure hundreds of columns in a table is the best design for your
> problem?
>

--


ˆÈ�ã�A‚æ‚낵‚*‚¨Šè‚¢‚¢‚½‚µ‚Ü‚·�B

---------------------------------------------------------
Zhao Xin
NEC-CAS Software Laboratories Co.,Ltd.
Tel : 8233-4433-425
Telnet : 8-0086-22-425
E-mail:�@zhaox@necas.nec.com.cn
--------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

zhaoxin schrieb:
> I have to face this trouble , it is not I want , but a historical problem .
> so , can you tell me ?

Try it out. I'd change the future of that history though.

You can expect much better performany on virtually any
RDBMS with appropriate schema.

++Tino

PS: try to send text-only to mailinglists

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

zhaoxin wrote:
> I have to face this trouble , it is not I want , but a historical problem .
> so , can you tell me ?

Sure, but you'll need to say what column-types you have.

Below is a small script to generate a table with lots of columns.

#!/bin/perl -w
use strict;

my $tbl = 'test_text';
my $typ = 'text';
my $num_cols = 1500;

print "CREATE TABLE $tbl (\n";
for (my $c=0; $c<$num_cols; $c++) {
print " col$c $typ,\n";
}
print "PRIMARY KEY (col0)\n";
print ");\n";
exit;

You can run it with something like:
perl mk_script.pl | psql -Urichardh richardh

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> Hi All.
>
> I have a question about the Maximum number of columns in a table ?
>
> In FAQ for PostgreSQL,I can find this description :
> Maximum number of columns in a table?
> 250-1600 depending on column types
> But , I want to know what type is 1600 limit , and what type is 250
> limit . it is important for me , thanks .

I'm pretty sure I've read the reason for the limit somewhere in the
source code, but I can't remember where. It's probably somewhere in
http://developer.postgresql.org/cvsw...l/src/include/. I know
that the exact upper limit isn't actually 1600 fields, it's more like
1643 or something.

But, as others have said, just try creating your table and see what
happens. If it fails, you might be able to get it to work by increasing
the block size.

And as others have said, this is almost certainly a horrible schema that
needs to be fixed, badly. Luckily, thanks to views and rules, you could
probably fix it without actually changing any of the client code.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> > Hi All.
> >
> > I have a question about the Maximum number of columns in a table ?
> >
> > In FAQ for PostgreSQL,I can find this description :
> > Maximum number of columns in a table?
> > 250-1600 depending on column types
> > But , I want to know what type is 1600 limit , and what type is 250
> > limit . it is important for me , thanks .
>
> I'm pretty sure I've read the reason for the limit somewhere in the
> source code, but I can't remember where. It's probably somewhere in
> http://developer.postgresql.org/cvsw...l/src/include/. I know
> that the exact upper limit isn't actually 1600 fields, it's more like
> 1643 or something.
>
> But, as others have said, just try creating your table and see what
> happens. If it fails, you might be able to get it to work by increasing
> the block size.
>
> And as others have said, this is almost certainly a horrible schema that
> needs to be fixed, badly. Luckily, thanks to views and rules, you could
> probably fix it without actually changing any of the client code.

The limit has to do with the fact that all the "header" info for each
column must fit in a single block (8K default).

I seem to recall someone stating that increasing block size to 16k or
32k could increase this number by about 2x or 4x. Not sure if it'll
work, but it might be worth the effort if you're stuck keeping some
legacy app happy long enough to replace it with a well designed system.

Oh to be able to travel back in time and smack people for designing 1600
column tables...

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Scott Marlowe wrote:
> On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote:
> > On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> > > Hi All.
> > >
> > > I have a question about the Maximum number of columns in a table ?
> > >
> > > In FAQ for PostgreSQL,I can find this description :
> > > Maximum number of columns in a table?
> > > 250-1600 depending on column types
> > > But , I want to know what type is 1600 limit , and what type is 250
> > > limit . it is important for me , thanks .
> >
> > I'm pretty sure I've read the reason for the limit somewhere in the
> > source code, but I can't remember where. It's probably somewhere in
> > http://developer.postgresql.org/cvsw...l/src/include/. I know
> > that the exact upper limit isn't actually 1600 fields, it's more like
> > 1643 or something.
> >
> > But, as others have said, just try creating your table and see what
> > happens. If it fails, you might be able to get it to work by increasing
> > the block size.
> >
> > And as others have said, this is almost certainly a horrible schema that
> > needs to be fixed, badly. Luckily, thanks to views and rules, you could
> > probably fix it without actually changing any of the client code.
>
> The limit has to do with the fact that all the "header" info for each
> column must fit in a single block (8K default).
>
> I seem to recall someone stating that increasing block size to 16k or
> 32k could increase this number by about 2x or 4x. Not sure if it'll
> work, but it might be worth the effort if you're stuck keeping some
> legacy app happy long enough to replace it with a well designed system.

Yes, that is correct. Increasing the block size can increase the
maximum number of columns. Certain columns like int4 are 4 bytes,
while text/varchar/char can be placed in toast tables so only the
pointer has to fix in the table, and I think the header is 8 bytes.

However, the fixed limit is 1600. Here is a comment from the code:

/*----------
* MaxHeapAttributeNumber limits the number of (user) columns in a table.
* This should be somewhat less than MaxTupleAttributeNumber. It must be
* at least one less, else we will fail to do UPDATEs on a maximal-width
* table (because UPDATE has to form working tuples that include CTID).
* In practice we want some additional daylight so that we can gracefully
* support operations that add hidden "resjunk" columns, for example
* SELECT * FROM wide_table ORDER BY foo, bar, baz.
* In any case, depending on column data types you will likely be running
* into the disk-block-based limit on overall tuple size if you have more
* than a thousand or so columns. TOAST won't help.
*----------
*/
#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match