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

Question

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.

Solution 1

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.

Solution 2

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

Comments

[Luke101]

Wow..I am very familiar with sql server and my first time using postgresql. I had no idea a database had hstore and json. You gave me a good idea to solve another problem by using these datatypes. I see postgresql is a powerful tool.

[Basil Bourque]

By the way, major new features for JSON support are coming with Postgres 9.4. Available now as a beta. XML support is already excellent. And Postgres has had key-value pair support (hstore) long before the "No-SQL" label was coined. Powerful indeed.