What is the maximum number of columns in a PostgreSQL select query

Question

Do you know how many columns can be requested in Postgresql? I need to know this before I start my project.

Answer 1

According to About PostgreSQL it is "250 - 1600 depending on the type of columns." See "Limits." Column types affect it because PostgreSQL rows can have no more than 8kb (one page), they cannot span pages. Larger column values ​​are OK because TOAST handles this, but there is a limit to the number of columns that you can fit in, depending on how widely non-displayable data types are used.

(Strictly speaking, this refers to columns that can be stored in rows on disk, and queries can have a wider range of columns than this. I do not recommend relying on it.)

If you are even considering approaching the borders of the columns, you are likely to have problems.

Matching tables with relational databases looks like the easiest thing in the world - matching columns to columns, rows, and rows. Right? In fact, spreadsheets are huge free-form monsters that don't provide any structure and can be really worthless. Relational databases are designed to handle more rows, but at a cost; in the case of PostgreSQL, part of this cost is a limitation on how wide it is for those rows. When faced with spreadsheets created by Joe User, this can be a real problem.

One “solution” is to decompose them into EAV , but it is unbearably slow and ugly to work. The best solutions use arrays where possible, composite types, hstore , json, xml, etc.

In the end, however, the best answer is to parse the table using a spreadsheet.

Answer 2

For others who can find this information, answer 1663 is useful depending on the column types associated with this post http://archives.postgresql.org/pgsql-admin/2008-05/msg00208.php

Answer 3

One reason for having a large number of columns is to store a large vector of words, starting with a text search.

Text output can produce 10,000 or more functions, as well as columns.

You can use a data warehouse, such as MonetDB , which states that "the number of columns per table is virtually unlimited."

Answer 4

Using the JSON / JSONB type, there is almost no need to have a lot of columns in the table.

And in rare cases, if you were to hit the maximum column limit of your database system, perhaps you could use an RDBMS implementation of a spreadsheet with the following table:

 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 an almost unlimited number of columns, but using it would be less trivial.