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 可以处理该问题，但是您可以容纳的列数有限制，这取决于所使用的未 TOAST 数据类型的宽度.

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.

将电子表格映射到关系数据库似乎是世界上最简单的事情 - 将列映射到列，将行映射到行，然后执行.对?实际上，电子表格是巨大的自由形式的怪物，没有结构，而且非常笨拙.关系数据库旨在处理很多行，但需要付出代价；在 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屋！