Do you know what the maximum number of columns that can be queried in Postgresql? I need to know this before I start my project.
45
votes
Possible Duplicate: stackoverflow.com/questions/12155777/…
– mawburn
I have a rare need to put a high amount of columns in the table. No way around it without causing serious pain.
– Luke101
While highly suspicious of database design problems, this is still a valid question. Related advice cannot replace the answer.
– Erwin Brandstetter
Do you need each one to be a unique column? Could serialized data or arrays help here?
– Scott Marlowe
Adding to @Scott's comment: .. or hstore, maybe?
– Erwin Brandstetter
3 Answers
55
votes
According to PostgreSQL Limits it's "250 - 1600 depending on column types". See note under the table. The column types affect it because in PostgreSQL rows may be at most 8kb (one page) wide, they cannot span pages. Big values in columns are OK because TOAST handles that, but there's a limit to how many columns you can fit in that depends on how wide the un-TOASTed data types used are.
(Strictly this refers to columns that can be stored in on-disk rows; queries might be able to use wider column sets than this. I don't recommend relying on it.)
If you're even thinking about approaching the column limits you're probably going to have issues.
Mapping spreadsheets to relational databases seems like the simplest thing in the world - map columns to columns, rows to rows, and go. Right? In reality, spreadsheets are huge freeform monsters that enforce no structure and can be really unweildy. Relational databases are designed to handle lots more rows, but at a cost; in the case of PostgreSQL part of that cost is a limitation to how wide it likes those rows to be. When facing spreadsheets created by Joe User this can be a real problem.
One "solution" is to decompose them into EAV, but that's unspeakably slow and ugly to work with. Better solutions are using arrays where possible, composite types, hstore, json, xml, etc.
In the end, though, sometimes the best answer is to analyse the spreadsheet using a spreadsheet.
8
votes
For others who might find this information useful the answer is 1663 depending on the types of columns occording to this post http://archives.postgresql.org/pgsql-admin/2008-05/msg00208.php
0
votes
With the use of the JSON/JSONB type, there almost is no need to have very many columns in a table.
And in rare cases, if you were to hit the maximum column limit of your database system, maybe you could use an RDBMS implementation of a spreadsheet, with a table like the following:
create table wide_table(
id serial not null primary key
,rownum integer not null
,colnum integer not null
,colname varchar(30) not null
,coltype varchar(30) not null
,nullable boolean not null
,collen integer
,colprec integer
,colscale integer
,colvalue raw(2000)
,unique (rownum,colnum)
);
This would allow for practically unlimited number of columns, but using it would be less trivial.
