pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Column limits in table/ views
3+ messages / 3 participants
[nested] [flat]
* Re: Column limits in table/ views
@ 2003-06-07 13:48 Rod Taylor <rbt@rbt.ca>
1 sibling, 1 reply; 3+ messages in thread
From: Rod Taylor @ 2003-06-07 13:48 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; +Cc: pgsql-sql
On Fri, 2003-06-06 at 18:02, A.M. wrote:
> I have 560 columns of NUMERIC(10,14). To not run up against max column
> restraints, I split the information into two tables. Does the column
> limit on tables imply the same limit for views or selects or could I
> potentially select a row across both tables and make a view that hides
> the split?
The limit is documented as having to do with the size of a block. The
largest a single row can become is 8k with primitive types (text, and
other variable length types will 'overflow' into another area -- see
TOAST).
Selects should be able to return more columns than that. Both views and
tables create a ROW TYPE to represent it, which is where the limitation
is. Thus, I would not expect you to be able to create a view.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Column limits in table/ views
@ 2003-06-07 18:08 Gaetano Mendola <gmendola@mbigroup.it>
1 sibling, 0 replies; 3+ messages in thread
From: Gaetano Mendola @ 2003-06-07 18:08 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; pgsql-sql
"A.M." <agentm@cmu.edu> wrote:
> I have 560 columns of NUMERIC(10,14). To not run up against max column
> restraints, I split the information into two tables. Does the column
> limit on tables imply the same limit for views or selects or could I
> potentially select a row across both tables and make a view that hides
> the split?
Hi,
just for curiosity, can I known why do you need a table or a view
with more then 560 Columns ?
Usually have a big table like yours is sign of a not good design.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Column limits in table/ views
@ 2003-06-09 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-06-09 14:22 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: A.M. <agentm@cmu.edu>; pgsql-sql
Rod Taylor <rbt@rbt.ca> writes:
> On Fri, 2003-06-06 at 18:02, A.M. wrote:
>> I have 560 columns of NUMERIC(10,14). To not run up against max column=20
>> restraints, I split the information into two tables. Does the column=20
>> limit on tables imply the same limit for views or selects or could I=20
>> potentially select a row across both tables and make a view that hides=20
>> the split?
> The limit is documented as having to do with the size of a block. The
> largest a single row can become is 8k with primitive types (text, and
> other variable length types will 'overflow' into another area -- see
> TOAST).
> Selects should be able to return more columns than that. Both views and
> tables create a ROW TYPE to represent it, which is where the limitation
> is. Thus, I would not expect you to be able to create a view.
But a view row is never stored on disk, so the block-size limit doesn't
come into play. You will still be constrained by the max column count
(1600), because that comes from the tuple header layout --- the size of
the header plus null bitmap has to fit into a uint8 field. But a view
with 560 columns would work.
I think some performance issues might come up if you actually try to
select all the columns at once, because there are places whose behavior
is O(N^2) in the number of columns. Dunno whether this effect will be
noticeable with ~560 columns though.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-09 14:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-07 13:48 ` Rod Taylor <rbt@rbt.ca>
2003-06-09 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-07 18:08 ` Gaetano Mendola <gmendola@mbigroup.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox