PostgreSQL选择查询中的最大列数是多少 [英] What is the maximum number of columns in a PostgreSQL select query

问题描述

您知道在Postgresql中可以查询的最大列数吗?在开始我的项目之前，我需要知道这一点.

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.

推荐答案

根据关于PostgreSQL ，它是"250-1600，取决于在列类型上".请参阅限制".列类型会影响它，因为在PostgreSQL中，行的宽度最多为8kb(一页)，它们不能跨越页面.列中的大值是可以的，因为TOAST可以处理此问题，但是可以容纳多少列是有限制的，这取决于所使用的未TOASTed数据类型的宽度.

According to About PostgreSQL it's "250 - 1600 depending on column types". See "Limits". 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.

将电子表格映射到关系数据库似乎是世界上最简单的事情-将列映射为列，将行映射为行，然后运行.正确的?实际上，电子表格是巨大的自由形式的庞然大物，它们不强制执行任何结构，而且确实很笨拙.关系数据库旨在处理更多的 lot 行，但要付出一定的代价；在PostgreSQL的情况下，该成本的一部分是对它喜欢这些行的宽度的限制.当面对由Joe User创建的电子表格时，这可能是一个真正的问题.

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.

一种解决方案"是将它们分解为 EAV ，但这工作起来非常缓慢且丑陋.更好的解决方案是在可能的情况下使用数组，复合类型，hstore，json，xml等.

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.

这篇关于PostgreSQL选择查询中的最大列数是多少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！