PostgreSQL 主键长度限制
主键列的长度限制是多少？我将使用 varchar 作为主键。我没有找到任何信息，它可以多长，因为 PostgreSQL 在用作主键时不需要指定 varchar 限制？
What is the limit of the length of primary key column? I'm going to use varchar as primary key. I've found no info, how long it can be, since PostgreSQL does not require to specify varchar limit when used as primary key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
B 树索引中值的最大长度（包括主键）是缓冲区页大小的三分之一，默认情况下，floor(8192/3) = 2730 字节。
The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.
我相信最大 varchar 长度是 Postgres 配置设置。不过，看起来大小不能超过1GB。
http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table .2C_and_a_database.3F
话虽如此，将大型 varchar 列作为主键可能不是一个好主意。考虑使用串行或大串行（http://www. postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-SERIAL）
I believe that maximum varchar length is a Postgres configuration setting. However, it looks as though it can't exceed 1GB in size.
http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F
That having been said, it's probably not a good idea to have a large varchar column as a primary key. Consider using a serial or bigserial (http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-SERIAL)
你应该做一个测试。
我已经在 PostgreSQL 8.4 上使用表进行了测试，该表将单个 varchar 列作为主键。结果是，我能够存储 235000 个 ASCII 字符、116000 个波兰语变音字符（fg 'ć'）或 75000 个中文（fg '汉'）。对于更大的集合，我收到一条消息：
BŁĄD：索引行大小 5404 超过 btree 最大值 2712
但是，消息表明：
无法对大于缓冲区页 1/3 的值建立索引。
因此这些值是允许的，但并非整个字符串用于唯一性检查。
嗯，您可以在该列中放入大量数据。然而，如上所述，如果您必须使用这么长的值作为键，那么您的设计就很糟糕。您应该使用人工主键。
You should made a test.
I've made tests, with table, that have single varchar column as primary key, on PostgreSQL 8.4. The result is, that I was able to store 235000 ASCII characters, 116000 polish diactrical characters (f.g. 'ć') or 75000 chinese (f.g. '汉'). For larger sets I've got a message:
BŁĄD: index row size 5404 exceeds btree maximum, 2712
However, the message told that:
Values larger than 1/3 of a buffer page cannot be indexed.
So the values were allowed, however not the whole string was used for uniqueness check.
Well, this is a very large amount of data that you can put in that column. However, as noted above, your design is poor if you will have to use such long values as keys. You should use artificial primary key.